Search for current date in a text field
in [Queries]
|
From: mrlewis52 on 26 Apr 2010 10:24 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. The problem is that the field that needs to be searched is in a text format "yyyy-mm-dd". I have used the following statement successfully to pull out current year: Left([ArrestDate],4)=DatePart("yyyy",Date()). This does pull off any records with the 2010 year. However, I need to sort by the entire date. How can I do this? I have tried the following hoping it would put a string together but I get an "Invalid Procedure Call" error.
From: vanderghast on 26 Apr 2010 10:51 *IF* your field is a date_time field, you can filter by a criteria like: >= DateSerial(2010, 1, 1,) AND < DateSerial(2011, 1, 1) and if there is an index on that field, the index should be used. Note that there is a difference between HOW IT IS STORED and HOW IT IS DISPLAYED. It is not because you SEE a format yyyy-mm-dd that it is a stored as a string (even if it MAY be a string too). That is how it is important to know if the field is a date_time field or if it is a string. Since you got an error using LEFT(fieldName, 4), I assume the field IS NOT a string value, but a date_time value, which is ok. You can use >= DateSerial(YEAR(NOW), 1, 1,) AND < DateSerial(1+YEAR(NOW), 1, 1) if the year to consider is to be the actual one, and plan to use the application for some years to come. Vanderghast, Access MVP "mrlewis52" <u59618(a)uwe> wrote in message news:a71c657b305ed(a)uwe... >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. > > The problem is that the field that needs to be searched is in a text > format > "yyyy-mm-dd". I have used the following statement successfully to pull out > current year: > > Left([ArrestDate],4)=DatePart("yyyy",Date()). > > This does pull off any records with the 2010 year. However, I need to sort > by > the entire date. > > How can I do this? > > I have tried the following hoping it would put a string together but I get > an > "Invalid Procedure Call" error. >
From: Daryl S on 26 Apr 2010 11:15 Mrlewis52 - You can turn the string value into a date value using the DateValue function, but you should pass in the string date in the same order as your system date - usually month/day/year. In your query that pulls the records you want, you can also convert the arrest date to a true date field, or if you only need to sort the records you are returning, you can sort based on the converted true date. This is the expression that will give you a true date based on the yyyy-mm-dd format: DateValue(Mid([ArrestDate],6,2) & "/" & Right([ArrestDate],2) & "/" & Left([ArrestDate],4)) -- Daryl S "mrlewis52" 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. > > The problem is that the field that needs to be searched is in a text format > "yyyy-mm-dd". I have used the following statement successfully to pull out > current year: > > Left([ArrestDate],4)=DatePart("yyyy",Date()). > > This does pull off any records with the 2010 year. However, I need to sort by > the entire date. > > How can I do this? > > I have tried the following hoping it would put a string together but I get an > "Invalid Procedure Call" error. > > . >
From: Jerry Whittle on 26 Apr 2010 11:20 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "mrlewis52" 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. > > The problem is that the field that needs to be searched is in a text format > "yyyy-mm-dd". I have used the following statement successfully to pull out > current year: > > Left([ArrestDate],4)=DatePart("yyyy",Date()). > > This does pull off any records with the 2010 year. However, I need to sort by > the entire date. > > How can I do this? > > I have tried the following hoping it would put a string together but I get an > "Invalid Procedure Call" error. > > . >
From: John W. Vinson on 26 Apr 2010 11:43 On Mon, 26 Apr 2010 14:24:12 GMT, "mrlewis52" <u59618(a)uwe> 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. > >The problem is that the field that needs to be searched is in a text format >"yyyy-mm-dd". I have used the following statement successfully to pull out >current year: > >Left([ArrestDate],4)=DatePart("yyyy",Date()). > >This does pull off any records with the 2010 year. However, I need to sort by >the entire date. > >How can I do this? > >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") -- John W. Vinson [MVP]
|
Next
|
Last
Pages: 1 2 Prev: Query to see what percentage of people scored a specific number Next: crosstab for day of week |