Help with code in control source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created this code in a control source for text box
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= Date() -6 ")

Basically this is totalling the number of students from 5 to 7pm and on the
current date -6 days.

I need help changing the end of this code. Instead of using date() I would
like it to use the date entered from a date parameter query. So, if I enter
10/07/05 in the parameter popup It would then subtract 6 days from that for
the value.

I tried this basic change but got #error for the value
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= -6 ")

If this is not possible can I have a unbound textbox where the user would
enter a date then refresh and then code would subtract 6 days from the text
boxes value?

The part of the code that creates a total between times works fine

Thank you
David
 
I created this code in a control source for text box
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= Date() -6 ")

Basically this is totalling the number of students from 5 to 7pm and on the
current date -6 days.

I need help changing the end of this code. Instead of using date() I would
like it to use the date entered from a date parameter query. So, if I enter
10/07/05 in the parameter popup It would then subtract 6 days from that for
the value.

I tried this basic change but got #error for the value
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= -6 ")

If this is not possible can I have a unbound textbox where the user would
enter a date then refresh and then code would subtract 6 days from the text
boxes value?

The part of the code that creates a total between times works fine

Thank you
David
Click to expand...

Two things:
1) Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

2) Do I understand correctlythat you wish to be prompted for the
number of days to enter?

After you change the date field's name to something else (let's say
[SaleDate], try:

=DCount("*","SignInQry"," [TimeIn] Between #5:00:00 pm# and
#7:59:59 pm# and [SaleDate]= DateDiff(,"d",-[Enter how many
days],Date()) ")

If you mean something else, then you'll need to better explain what,
when, and why.
 
Hello, thank you for the reply

Ok, I would like to have an unbound textbox(Textbox1) on the form that the
user enters the date. Now based of the date entered in textbox1. Another
textbox(Textbox2) will display a relivant value based off the date entered in
textbox1, between 9 and 10am. The value would be the total number of
students in that hour for the entered date.

Here is some examples of code I tried but did not work.
=DCount("[Fname]","UsageTimeQry"," [TimeIn] Between #09:00:00am# and
#10:59:59am# and [DateIn]= #me.texbox1# ")

=DCount("[Fname]","UsageTimeQry"," [TimeIn] Between #09:00:00am# and
#10:59:59am# and [DateIn]= me.texbox1 ")

Now if I manually put the date into the code it works, but I need to have
the user enter the date.

I hope this makes sense, thank you again
David
fredg said:
I created this code in a control source for text box
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= Date() -6 ")

Basically this is totalling the number of students from 5 to 7pm and on the
current date -6 days.

I need help changing the end of this code. Instead of using date() I would
like it to use the date entered from a date parameter query. So, if I enter
10/07/05 in the parameter popup It would then subtract 6 days from that for
the value.

I tried this basic change but got #error for the value
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= -6 ")

If this is not possible can I have a unbound textbox where the user would
enter a date then refresh and then code would subtract 6 days from the text
boxes value?

The part of the code that creates a total between times works fine

Thank you
David
Click to expand...

Two things:
1) Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

2) Do I understand correctlythat you wish to be prompted for the
number of days to enter?

After you change the date field's name to something else (let's say
[SaleDate], try:

=DCount("*","SignInQry"," [TimeIn] Between #5:00:00 pm# and
#7:59:59 pm# and [SaleDate]= DateDiff(,"d",-[Enter how many
days],Date()) ")

If you mean something else, then you'll need to better explain what,
when, and why.
Click to expand...
 
Hello, thank you for the reply

Ok, I would like to have an unbound textbox(Textbox1) on the form that the
user enters the date. Now based of the date entered in textbox1. Another
textbox(Textbox2) will display a relivant value based off the date entered in
textbox1, between 9 and 10am. The value would be the total number of
students in that hour for the entered date.

Here is some examples of code I tried but did not work.
=DCount("[Fname]","UsageTimeQry"," [TimeIn] Between #09:00:00am# and
#10:59:59am# and [DateIn]= #me.texbox1# ")

=DCount("[Fname]","UsageTimeQry"," [TimeIn] Between #09:00:00am# and
#10:59:59am# and [DateIn]= me.texbox1 ")

Now if I manually put the date into the code it works, but I need to have
the user enter the date.

I hope this makes sense, thank you again
David
fredg said:
I created this code in a control source for text box
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= Date() -6 ")

Basically this is totalling the number of students from 5 to 7pm and on the
current date -6 days.

I need help changing the end of this code. Instead of using date() I would
like it to use the date entered from a date parameter query. So, if I enter
10/07/05 in the parameter popup It would then subtract 6 days from that for
the value.

I tried this basic change but got #error for the value
=DCount("[Fname]","SignInQry"," [TimeIn] Between #5:00:00pm# and_
#7:59:59pm# and [Date]= -6 ")

If this is not possible can I have a unbound textbox where the user would
enter a date then refresh and then code would subtract 6 days from the text
boxes value?

The part of the code that creates a total between times works fine

Thank you
David
Click to expand...

Two things:
1) Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

2) Do I understand correctlythat you wish to be prompted for the
number of days to enter?

After you change the date field's name to something else (let's say
[SaleDate], try:

=DCount("*","SignInQry"," [TimeIn] Between #5:00:00 pm# and
#7:59:59 pm# and [SaleDate]= DateDiff(,"d",-[Enter how many
days],Date()) ")

If you mean something else, then you'll need to better explain what,
when, and why.
Click to expand...
Click to expand...
When using DCount you are counting all records that match the
criteria, not [Fname], so use the "*" wildcard character.
The Me.TexBox (is that the correct spelling you want?) must be outside
the string and concatenated into it.

=DCount("*","UsageTimeQry"," [TimeIn] Between #09:00:00 am# and
#10:59:59 am# and [DateIn]= #" & me.texbox1 & "#")
 
Back
Top