Prev: Query to see what percentage of people scored a specific number
Next: crosstab for day of week
From: mrlewis52 on 26 Apr 2010 11:50
I think this might just work.

Jerry Whittle wrote:
>The CDate function will convert a string like that into a date.
>
>Debug.Print Cdate("2010-04-25")
>4/25/2010
>
>CDate([ArrestDate])
>
>However CDate will fail if it's presented with an invalid date.
>"2010-13-13" won't work. You might want to check the data field with the
>IsDate function to see if there are any problem records.
>> I have a SQL database that I have an external data link to an access database.
>> I need to search records based on the current date.
>[quoted text clipped - 14 lines]
>>
>> .

From: mrlewis52 on 26 Apr 2010 12:11
When I added your suggestion for the criteria, I am getting an "Invalid use
of Null" error. I entered the line on the Criteria line for the [ArrestDate]
field.

CDate([ArrestDate]) = Date()

What am I missing? Does this error indicate there is an invalid date or
something in the data?


John W. Vinson wrote:
>>I have a SQL database that I have an external data link to an access database.
>>I need to search records based on the current date.
>[quoted text clipped - 12 lines]
>>I have tried the following hoping it would put a string together but I get an
>>"Invalid Procedure Call" error.
>
>I'm in agreement with Vanderghast: is this *really* a Text type field, or a
>Date/Time field just formatted and displayed as text?
>
>If it is text, you should be able to use a criterion
>
>CDate([ArrestDate]) = Date()
>
>to recast the text string into a Date/Time and compare it with today's date.
>For more flexiblity, and to take advantage of any indexes on ArrestDate, you
>could use a small unbound form frmCrit with two textboxes txtStart and txtEnd
>(these could even have =Date() as their default value to save typing if
>today's data is the most common search); you'ld use a criterion like
>
>[ArrestDate] BETWEEN Format([Forms]![frmCrit]![txtStart], "yyyy-mm-dd") AND
>Format([Forms]![frmCrit]![txtEnd], "yyyy-mm-dd")
>

From: mrlewis52 on 26 Apr 2010 12:19
There are records that have no dates in them which results in an #Error in
the CDate field.

mrlewis52 wrote:
>When I added your suggestion for the criteria, I am getting an "Invalid use
>of Null" error. I entered the line on the Criteria line for the [ArrestDate]
>field.
>
>CDate([ArrestDate]) = Date()
>
>What am I missing? Does this error indicate there is an invalid date or
>something in the data?
>
>>>I have a SQL database that I have an external data link to an access database.
>>>I need to search records based on the current date.
>[quoted text clipped - 17 lines]
>>[ArrestDate] BETWEEN Format([Forms]![frmCrit]![txtStart], "yyyy-mm-dd") AND
>>Format([Forms]![frmCrit]![txtEnd], "yyyy-mm-dd")

From: mrlewis52 on 26 Apr 2010 12:39
I used Mr. Vinson and your suggestion with the CDate and it worked great.
Thanks very much.

Jerry Whittle wrote:
>The CDate function will convert a string like that into a date.
>
>Debug.Print Cdate("2010-04-25")
>4/25/2010
>
>CDate([ArrestDate])
>
>However CDate will fail if it's presented with an invalid date.
>"2010-13-13" won't work. You might want to check the data field with the
>IsDate function to see if there are any problem records.
>> I have a SQL database that I have an external data link to an access database.
>> I need to search records based on the current date.
>[quoted text clipped - 14 lines]
>>
>> .

From: mrlewis52 on 26 Apr 2010 12:38
The blank ArrestDate fields were the issue. Took those out and it works great!
Thanks for your help (and Vanderghast).

mrlewis52 wrote:
>There are records that have no dates in them which results in an #Error in
>the CDate field.
>
>>When I added your suggestion for the criteria, I am getting an "Invalid use
>>of Null" error. I entered the line on the Criteria line for the [ArrestDate]
>[quoted text clipped - 10 lines]
>>>[ArrestDate] BETWEEN Format([Forms]![frmCrit]![txtStart], "yyyy-mm-dd") AND
>>>Format([Forms]![frmCrit]![txtEnd], "yyyy-mm-dd")

First  |  Prev  | 
Pages: 1 2
Prev: Query to see what percentage of people scored a specific number
Next: crosstab for day of week