date string

  • Thread starter Thread starter Chrissy
  • Start date Start date
C

Chrissy

What is wrong with the date portion of this code?

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """" And
"[SinceDate] between #" & [Forms]![9frmDateSelector]![txtStartDate] & "# and
#" & [Forms]![9frmDateSelector]![txtEndDate] & "#",True)


If I remove the date portion -- only counting [Source] -- the count is
correct.

If I leave it as it is above, the count is ALL clients -- no qualification
for [Source] or date range.

So, I know it is wrong for the dates. But...what is correct? Is it the
quotes?

I appreciate the help.
 
looks like you have an extra double quote in there. try

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """" And
[SinceDate] Between #" & [Forms]![9frmDateSelector]![txtStartDate] & "# And
#" & [Forms]![9frmDateSelector]![txtEndDate] & "#",True)

notice i removed the double quote directly before [SinceDate]. also, i'm
trying to remember if Between...And... will work in a domain aggregate
function. try the above first, and if you still have no joy, try

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """" And
[SinceDate] >= #" & [Forms]![9frmDateSelector]![txtStartDate] & "# And
[SinceDate] <= #" & [Forms]![9frmDateSelector]![txtEndDate] & "#",True)

hth
 
Try the following. TOO many quotes in the third argument.

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] &
""" And [SinceDate] between #" &
[Forms]![9frmDateSelector]![txtStartDate] &
"# and #" & [Forms]![9frmDateSelector]![txtEndDate] & "#",True)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Try opening the code window and in the immmediate window enter this on one
line followed by <enter>
Youe form will need to be open to read the date field.

debug.print "Source]=""" & [Source] & """" And
"[SinceDate] between #" & [Forms]![9frmDateSelector]![txtStartDate] & "# and
#" & [Forms]![9frmDateSelector]![txtEndDate] & "#"

Look closely at what prints out to see if there are any obvious errors in
the format. Also, try using a single quote around strings rather than a
double quote.
Mich

Chrissy said:
Thanks.

The control will not accept the expression...

"The expression you entered has an invalid date value."

--
Chrissy


tina said:
looks like you have an extra double quote in there. try

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """" And
[SinceDate] Between #" & [Forms]![9frmDateSelector]![txtStartDate] & "#
And
#" & [Forms]![9frmDateSelector]![txtEndDate] & "#",True)

notice i removed the double quote directly before [SinceDate]. also, i'm
trying to remember if Between...And... will work in a domain aggregate
function. try the above first, and if you still have no joy, try

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """" And
[SinceDate] >= #" & [Forms]![9frmDateSelector]![txtStartDate] & "# And
[SinceDate] <= #" & [Forms]![9frmDateSelector]![txtEndDate] & "#",True)

hth


Chrissy said:
What is wrong with the date portion of this code?

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """"
And
"[SinceDate] between #" & [Forms]![9frmDateSelector]![txtStartDate] &
"# and
#" & [Forms]![9frmDateSelector]![txtEndDate] & "#",True)


If I remove the date portion -- only counting [Source] -- the count is
correct.

If I leave it as it is above, the count is ALL clients -- no
qualification
for [Source] or date range.

So, I know it is wrong for the dates. But...what is correct? Is it
the
quotes?

I appreciate the help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top