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 |