Assigning a control source to query returns #name

[Arvin Meyer [MVP]]

My first guess would be that you have named the module and the function with
the same name. Change the module name to something like:

basDateFunctions

and recompile the database.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I have created the query in both design mode and it SQL view and can't get
it to work yet. I think I have it correct in the design view - from your
example, but it comes up with


'IsWeekDay as an undefined function'. Here's the module it's referring to

Public Function IsWeekday(dtmIn As Variant) As Boolean
On Error Resume Next
Dim intDay As Integer

intDay = Weekday(dtmIn)

Select Case intDay
Case 1, 7
IsWeekday = False
Case 2, 3, 4, 5, 6
IsWeekday = True
Case Else
End Select
End Function


Here is the SQL view of the query;

I've tried it in both Design mode and SQL view.
SELECT Avg([qryAmiCompl.Endtime]) AS AvgOfWeekday
FROM qryAmiCompl
WHERE ((IsWeekday([EndTime])=True));

I have used tables and queries and it always comes up with the same.

Hope you can help.

Thank you,

The default view, when opening a new query is the graphical grid design
view. Since I cannot represent that here, I just show the SQL code view,
which is what is generated by the graphical view, or by typing it out. If
you have tables and fields with the same names as my sample code, paste
that
code into a SQL window, then look at it in design view.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

You make this look so easy, and with the information that you've given
me
I
should be able to get this now.

I will have to learn how to use SQL, I'll need it at work.

So what you were saying in the past post on how it looks in an SQL
query,
you're say that the query is done with with sql just using code only
and
not
within the query. Correct?

Thanks again Arvin! You sure helped me out!

Kelly
A+ CNA
PC/Network Admin - Windows2003
IT Technical Support

:

OK Let's analyze this SQL syntax:

SELECT

Avg(tblMyData.Price) AS AvgOfPrice

is the same as looking at this in a query column:

AvgOfPrice: Avg(tblMyData.Price)

FROM

tblMyData

is the table name.

WHERE (((IsWeekDay([DateField]))=True));

This line looks like this in the criteria box of a Totals query:

IsWeekDay([DateField])=True

and the "Where" value is chosen. You must also turn off the visible
checkbox.

The semi-colon is added to tell Access that it's done with the
SQL-Statement. If you don't put it in, Access generally will when it
compiles the query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin, also on your query example, is the semi-colon required at the
end
of
the "Where" line or was that a typo.

Thanks again!

:

First of all. I'd write a little function like this:

Public Function IsWeekday(dtmIn As Variant) As Boolean
On Error Resume Next
Dim intDay As Integer

intDay = Weekday(dtmIn)

Select Case intDay
Case 1, 7
IsWeekday = False
Case 2, 3, 4, 5, 6
IsWeekday = True
Case Else
End Select
End Function


and put it in a Standard Module. Then I'd add a column to the query
like:

SELECT Avg(tblMyData.Price) AS AvgOfPrice
FROM tblMyData
WHERE (((IsWeekDay([DateField]))=True));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Man I wish I knew half as much as you guys do. I guess a person
has
to
start
somewhere. I really appreciate all the support this site has to
offer.

Anyway, I was able to get this with the creating of a list box,
thanks
to
the help you guys offered.

On the Average for this field, if the value is based on hours, is
there
a
way to exclude the weekend hours?

:

I prefer recordsets, but DLookup might be easier (this is
untested
code):

Sub Form_Current()
Dim x As Variant ' in case it is null

x = DLookup("MyColumn", "MYQuery", "IDField =
Forms!MyForm!txtID")

If Len(x & vbNullString) > 0 Then
Me.txtWhatever = x
End If

End Sub

If the query is returning only 1 record, you won't need the:

"IDField = Forms!MyForm!txtID"

part of DLookup
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Also, I did try the DLookup and it wouldn't return the value
to
the
form.
I
could only get the DLookup to work in the query, and there it
gave
me
the
value.

I also, have no SQL or VB programming experience but I can
follow
what's
going on. Amyway, about these to lines of example code. Can
they
be
used
to
get information from a query?

Me.MyLabel.Caption = "Lucy, I'm home!"

Me.MyLabel.Caption = Me.cboWhatever.Column(3)

Thanks!

:

Labels don't have a control source. They do have a caption
property
but
you
cannot assign anything more than text to it.:

Me.MyLabel.Caption = "Lucy, I'm home!"

Me.MyLabel.Caption = Me.cboWhatever.Column(3)

will also work, but you must do it in code in the combo's
AfterUpdate
event.
You code do a DLookup and return the value to a variable,
which
could
then
be assigned to the label's caption property.

Most developers would use a text box for this anyway. Is
there
some
reason
you want to use a label?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message
I'm assigning a control source in a label to point to a
query.
The
control
has this in it

=[qryAmiAvg]![AvgOfMax Of Elapsed Time]

The AvgOfMax Of Elapsed Time is a result from another
query,
I'm
not
sure
if
that matters?

Thanks!

 

[netadmin]

Arvin,

I would just like to say "Thanks"! That worked great once I did it with the
right source. It worked absolutely perfect!!

Thanks again!

The default view, when opening a new query is the graphical grid design
view. Since I cannot represent that here, I just show the SQL code view,
which is what is generated by the graphical view, or by typing it out. If
you have tables and fields with the same names as my sample code, paste that
code into a SQL window, then look at it in design view.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

You make this look so easy, and with the information that you've given me
I
should be able to get this now.

I will have to learn how to use SQL, I'll need it at work.

So what you were saying in the past post on how it looks in an SQL query,
you're say that the query is done with with sql just using code only and
not
within the query. Correct?

Thanks again Arvin! You sure helped me out!

Kelly
A+ CNA
PC/Network Admin - Windows2003
IT Technical Support

OK Let's analyze this SQL syntax:

SELECT

Avg(tblMyData.Price) AS AvgOfPrice

is the same as looking at this in a query column:

AvgOfPrice: Avg(tblMyData.Price)

FROM

tblMyData

is the table name.

WHERE (((IsWeekDay([DateField]))=True));

This line looks like this in the criteria box of a Totals query:

IsWeekDay([DateField])=True

and the "Where" value is chosen. You must also turn off the visible
checkbox.

The semi-colon is added to tell Access that it's done with the
SQL-Statement. If you don't put it in, Access generally will when it
compiles the query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin, also on your query example, is the semi-colon required at the
end
of
the "Where" line or was that a typo.

Thanks again!

:

First of all. I'd write a little function like this:

Public Function IsWeekday(dtmIn As Variant) As Boolean
On Error Resume Next
Dim intDay As Integer

intDay = Weekday(dtmIn)

Select Case intDay
Case 1, 7
IsWeekday = False
Case 2, 3, 4, 5, 6
IsWeekday = True
Case Else
End Select
End Function


and put it in a Standard Module. Then I'd add a column to the query
like:

SELECT Avg(tblMyData.Price) AS AvgOfPrice
FROM tblMyData
WHERE (((IsWeekDay([DateField]))=True));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Man I wish I knew half as much as you guys do. I guess a person has
to
start
somewhere. I really appreciate all the support this site has to
offer.

Anyway, I was able to get this with the creating of a list box,
thanks
to
the help you guys offered.

On the Average for this field, if the value is based on hours, is
there
a
way to exclude the weekend hours?

:

I prefer recordsets, but DLookup might be easier (this is untested
code):

Sub Form_Current()
Dim x As Variant ' in case it is null

x = DLookup("MyColumn", "MYQuery", "IDField = Forms!MyForm!txtID")

If Len(x & vbNullString) > 0 Then
Me.txtWhatever = x
End If

End Sub

If the query is returning only 1 record, you won't need the:

"IDField = Forms!MyForm!txtID"

part of DLookup
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Also, I did try the DLookup and it wouldn't return the value to
the
form.
I
could only get the DLookup to work in the query, and there it
gave
me
the
value.

I also, have no SQL or VB programming experience but I can follow
what's
going on. Amyway, about these to lines of example code. Can they
be
used
to
get information from a query?

Me.MyLabel.Caption = "Lucy, I'm home!"

Me.MyLabel.Caption = Me.cboWhatever.Column(3)

Thanks!

:

Labels don't have a control source. They do have a caption
property
but
you
cannot assign anything more than text to it.:

Me.MyLabel.Caption = "Lucy, I'm home!"

Me.MyLabel.Caption = Me.cboWhatever.Column(3)

will also work, but you must do it in code in the combo's
AfterUpdate
event.
You code do a DLookup and return the value to a variable, which
could
then
be assigned to the label's caption property.

Most developers would use a text box for this anyway. Is there
some
reason
you want to use a label?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm assigning a control source in a label to point to a query.
The
control
has this in it

=[qryAmiAvg]![AvgOfMax Of Elapsed Time]

The AvgOfMax Of Elapsed Time is a result from another query,
I'm
not
sure
if
that matters?

Thanks!