Dlookup problem

W

woknick

Registered User.
Local time
Today, 03:54
Joined
Sep 25, 2004
Messages
85
I have a Dlookup below:
IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound'AND [CallDate] >= Forms!Retriever![txtDate]AND [CallDate]<= Forms!Retriever![txtEndDate]")

txtDate and txtEndDate is a date in this format: 1/26/2005

unfortunatly [CallType] is a date in this format: 1/26/2005 7:45:00 AM

how do I compare the two so the Dlookup will return the count i want.

when I run the Dlookup now it returns 0 instead of 20

thanks
nick
 
the date function does not seem to work in the dlookup.
 
use "#"

woknick said:
the date function does not seem to work in the dlookup.
Click to expand...

When using a date in a Domain function, use # before and after your date.

Example :
IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound' AND [CallDate] >= #" & Forms!Retriever![txtDate] & "# AND [CallDate]<= #" & Forms!Retriever![txtEndDate] & "#")
 
Last edited:
so how would that work in this
IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound' AND DateValue([CallDate]) Between Forms!Retriever![txtDate]AND Forms!Retriever![txtEndDate]")


thanks
Nick
 
Try formatting the date to dd-mm-yyyy, or mm-dd-yyyy, Microsoft Jet's date format. I'm not sure which one it is.

IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound' AND [CallDate] >= #" & format(Forms!Retriever![txtDate],"dd-mm-yyyy") & "# AND [CallDate]<= #" & format(Forms!Retriever![txtEndDate],"dd-mm-yyyy") & "#")
 
the calltype is in general format. the form fields are in Short format
 
CallDate is in general format and the form fields are in short
 
I tried this, but it is still not finding the records .

IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound WHERE' &[CallDate]& 'Between #' & Forms!Retriever![txtDate]& '0:0:1# AND'& [CallDate] & '#' & Forms!Retriever![txtEndDate]& '23:59:59#'")
 
woknick said:
I have a Dlookup below:
IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound'AND [CallDate] >= Forms!Retriever![txtDate]AND [CallDate]<= Forms!Retriever![txtEndDate]")

txtDate and txtEndDate is a date in this format: 1/26/2005

unfortunatly [CallType] is a date in this format: 1/26/2005 7:45:00 AM
Click to expand...
if [CallType] is a date, why do you do [CallType] = 'Inbound'???
 
woknick said:
I tried this, but it is still not finding the records .

IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound WHERE' &[CallDate]& 'Between #' & Forms!Retriever![txtDate]& '0:0:1# AND'& [CallDate] & '#' & Forms!Retriever![txtEndDate]& '23:59:59#'")
Click to expand...

You have several syntax and structure errors in the one above!

Try this:

DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType]='Inbound' AND [CallDate]>=#" & format(Forms!Retriever![txtDate],"mm-dd-yyyy") & "# AND [CallDate]<=#" & format(Forms!Retriever![txtEndDate],"mm-dd-yyyy") & "#")

If it doesn't work, you can email me your database (if its not too big), I'll check it for you.
 
Pat Hartman said:
4. The default date structure for Access is month, day, year. Access can properly interpret text string dates in day, month, year format only when there is no confusion so 13/1/5 would be interpreted as Jan 13. However 12/1/5 would be interpreted as Dec 1. So, the one thing you do NOT want to do is to turn your dates into strings when using them in conditions.
Click to expand...

Your not working with a french version of Access or on a french version of Windows!!!
As far as I know, Access uses the Window's regional setting for its prefered date format, but Microsoft Jet uses mm/dd/yyyy no matter what.
I might be wrong...
 
Originally posted by woknick
I have a Dlookup below:
IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound'AND [CallDate] >= Forms!Retriever![txtDate]AND [CallDate]<= Forms!Retriever![txtEndDate]")

txtDate and txtEndDate is a date in this format: 1/26/2005

unfortunatly [CallType] is a date in this format: 1/26/2005 7:45:00 AM
Click to expand...

Since [CallDate] contains also the time, as pointed out by Pat, to return the correct count, you need to use the DateValue() function to extract the dates for comparison.


These two work on both US and UK date systems:-

IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound' AND DateValue([CallDate]) BETWEEN CDATE(Forms!Retriever![txtDate]) AND CDATE(Forms!Retriever![txtEndDate])")

IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType]='Inbound' AND DateValue([CallDate])>=#" & Format(Forms!Retriever![txtDate], "mm-dd-yyyy") & "# AND DateValue([CallDate])<=#" & Format(Forms!Retriever![txtEndDate], "mm-dd-yyyy") & "#")


This works on systems using US date format:-

IntC = DCount("[CallType]", "dbo_tbl_DailyCall_Log", "[CallType] = 'Inbound' AND DateValue([CallDate]) BETWEEN #" & Forms!Retriever![txtDate] & "# AND #" & Forms!Retriever![txtEndDate] & "#")



They are not the only ones that work. Some other syntax works, too.

Note: By default, Access treats any dates surrounded by the # signs as in US date format.
.
 
Can someone tell me why?

I always find the concept of date/time ranges in Access rather difficult to understand.

I noted that DateValue([CallDate]) worked, but I want to know why
WHERE [CallDate] BETWEEN #1/25/2005# AND #1/26/2005# doesn't include the record 1/26/2005 7:45:00 AM

Can someone tell me why? Thanks
 
VVID said:
I always find the concept of date/time ranges in Access rather difficult to understand.

I noted that DateValue([CallDate]) worked, but I want to know why
WHERE [CallDate] BETWEEN #1/25/2005# AND #1/26/2005# doesn't include the record 1/26/2005 7:45:00 AM

Can someone tell me why? Thanks
Click to expand...

Because #1/26/2005# actually means #1/26/2005 12:00 AM#. Keep in mind, date values (misnamed, in my opinion; they should be called Datetime values to be more indicative of their nature) are numbers (read Pat's earlier post). The computer converts the dates to numbers for its calculations, so what it is seing is "Between 38373 and 38374". Or, between MIDNIGHT on January 25 2005 to MIDNIGHT January 26 2005. Since 9:00 AM is .375 of one day, January 26, 2005 at 9:00 AM = 38374.375, which is not between 38373 and 38374.

When I do date calculations, I routinely change the date values to a double datatype for ease of understanding, but it is not necessary, as long as you understand the concept of the date values. I am not a big fan of using Between, although others swear by it.
 
The following is taken from Access's help file:-
Ways to optimize query performance
  • Use the Between...And, the In, and the = operators on indexed fields.
Click to expand...

VVID,
I would like to offer a visual explanation.

The following are the results from a query sorted by [CallDate]:-
Code: 
[b]			      Internally Stored	   Using
     [CallDate] 	      CDbl([CallDate])	   DateValue([CallDate])[/b]
	
    12/31/1899			   1		   12/31/1899

     1/25/2005     	       38377		    1/25/2005
     1/25/2005 12:00:01 AM     38377.0000115741	    1/25/2005
     1/25/2005 11:59:59 AM     38377.4999884259	    1/25/2005
     1/25/2005 12:00:00 PM     38377.5		    1/25/2005
     1/25/2005 12:00:01 PM     38377.5000115741	    1/25/2005
     1/25/2005 11:59:59 PM     38377.9999884259	    1/25/2005

     1/26/2005     	       38378		    1/26/2005
     1/26/2005  7:45:00 AM     38378.3229166667	    1/26/2005
     1/26/2005 11:59:59 PM     38378.9999884259	    1/26/2005

From the results we can see that a datetime value is internally stored as a double precision number, with the integer portion representing the number of days since 12/31/1899 and the decimal portion representing the time of the day since mid-night.

1/25/2005 mid-night is stored as 38377, 1/25/2005 12:00:01 AM as 38377.0000115741,
1/26/2005 mid-night as 38378 and so on.

Hence to execute WHERE [CallDate] BETWEEN #1/25/2005# AND #1/26/2005#
is actually to execute WHERE [CallDate] BETWEEN 38377 AND 38378

Now since any records after 1/26/2005 mid-night are numerically greater than 38378, they would naturally be excluded.


Some people have suggested a solution of adding #23:59:59# to the end date of a date range, or extending the end date of a date range by 1 day but DateAdd() -1 second from it. These suggestions work.

From the third column in the query results we see that Access also has a DateValue() function that can correctly extract the dates from datetime values.


Hence we can do it with either
WHERE [CallDate] BETWEEN #1/25/2005# AND #1/26/2005 23:59:59#
or
WHERE DateValue([CallDate]) BETWEEN #1/25/2005# AND #1/26/2005#

But which one is better?


I think the second one is easier to write and understand. However, running functions does take time. And DateValue([CallDate]) can't take advantage of field index.

Hence, if the table is large, the first one, being without any functions, can obviously run very much faster and is therefore to be preferred. Besides, it can take advantage of field index.
.
 
Last edited:
You must log in or register to reply here.

Similar threads

T
Solved It takes too long to download data from server
Replies
16
Views
1,397
GPGeorge
GPGeorge
A
Secure Storage Model for Microsoft Access: Stream-Based Encrypted Data Architecture(for discussion)
Replies
5
Views
406
GPGeorge
GPGeorge
A
Filter Records
Replies
1
Views
794
June7
June7
K
'Next n' Looping rather than moving to the next record
Replies
16
Views
1,555
arnelgp
arnelgp
B
Avoid duplicate records (overlapping dates)
Replies
18
Views
1,853
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom