Assigning a control source to query returns #name

[netadmin]

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!

 

[Rick Brandt]

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!

You cannot use a query, table, or sql statement as a ControlSource. Check help
for the DLookup() function.

 

[Arvin Meyer [MVP]]

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?

 

[netadmin]

Thanks Rick, I will try the DLookup function again. I did try it, but maybe
something wasn't quite right with it. I'll work on it again.

Thank You!

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!

You cannot use a query, table, or sql statement as a ControlSource. Check help
for the DLookup() function.

 

[Rick Brandt]

Thanks Rick, I will try the DLookup function again. I did try it, but
maybe something wasn't quite right with it. I'll work on it again.

Well here is what you were trying...
=[qryAmiAvg]![AvgOfMax Of Elapsed Time]

....and here is what the DLookup() would look like...
=DLookup("[AvgOfMax Of Elapsed Time]", "[qryAmiAvg]")

 

[netadmin]

What I need to do is place a percentage value in a Form that I have
established from a query field. It is giving me the Average of another
column.
The Form also contains (2) subforms and it seems that I can only have a
total of (2) subforms in it. So I thought maybe by doing it this way.

Thank You!

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!

 

[netadmin]

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!

 

[Arvin Meyer [MVP]]

What I need to do is place a percentage value in a Form that I have
established from a query field. It is giving me the Average of another
column.
The Form also contains (2) subforms and it seems that I can only have a
total of (2) subforms in it. So I thought maybe by doing it this way.

I'm not sure how many subforms you can have in a form. It's a lot though.
You can also nest them 7 levels deep, although anything more than 3 is
probably ridiculous.

 

[Arvin Meyer [MVP]]

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!

 

[netadmin]

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.

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!

 

[Arvin Meyer [MVP]]

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.

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!

 

[netadmin]

Hmmm, that was easy! Simply amazing to me that is...

I'll have to give it my best. It so easy for me to lose focus on what I'm
trying to accomplish in this database I'm working on. Anyway, I'll give this
a try and let you know the status.

Thank You!
Kelly

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.

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!

 

[netadmin]

Rick,

Thanks for the help, it worked great. I spent 1 1/2 days trying to figure it
out and wasn't getting anywhere. Now I can move on to the next steps.

It's greatly appreciated, Thank You!

Thanks Rick, I will try the DLookup function again. I did try it, but
maybe something wasn't quite right with it. I'll work on it again.

Well here is what you were trying...
=[qryAmiAvg]![AvgOfMax Of Elapsed Time]

....and here is what the DLookup() would look like...
=DLookup("[AvgOfMax Of Elapsed Time]", "[qryAmiAvg]")

 

[netadmin]

Hi Arvin,

I tried to write last night, but the sending didn't work. Anyway, I was able
to create the module, but I'm having a problem with the query.

The problem is I think with not have a table created for the part# and end
time? I have a query for this. I created a table that results from the query,
but it prompt's for overwriting each time (even though I change it in the
Tools/Options).

Also, in your example for the query. Where do you put this in the query? The
Field area? and in the Total area, would these be set as an expression?

Thank you!

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.

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!

 

[netadmin]

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.

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!

 

[Arvin Meyer [MVP]]

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!

 

[netadmin]

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!

 

[Arvin Meyer [MVP]]

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!

 

[netadmin]

I'll do that! I'm curious to see the output. Thanks!

I'm kind of thinking that I may have to look at the way I'm collecting the
EndTime record. The initial design of the database started out simple, but
soon changed. I'm sure that happens all the time.

I'm missing a table for the EndTime. I can however, create a subform table
added to the main form. I would also like to collect the data that's created
from the EndTime query.

The query information in the table comes from clicking the 'Finish' button.

Is there a way to just record the time the button is clicked? I already
assigned an event to the 'on click' button. Can more information be added to
that event?

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!

 

[netadmin]

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

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!