Search for current date in a text field

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.

0
mrlewis52
4/26/2010 2:24:12 PM
access.queries 6343 articles. 1 followers. Follow

9 Replies
1145 Views

Similar Articles

[PageSpeed] 1

*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@uwe> wrote in message news:a71c657b305ed@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.
> 

0
vanderghast
4/26/2010 2:51:38 PM
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.
> 
> .
> 
0
Utf
4/26/2010 3:15:01 PM
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.
> 
> .
> 
0
Utf
4/26/2010 3:20:01 PM
On Mon, 26 Apr 2010 14:24:12 GMT, "mrlewis52" <u59618@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]
0
John
4/26/2010 3:43:21 PM
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]
>> 
>> .

0
mrlewis52
4/26/2010 3:50:48 PM
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")
>

0
mrlewis52
4/26/2010 4:11:51 PM
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")

0
mrlewis52
4/26/2010 4:19:23 PM
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")

0
mrlewis52
4/26/2010 4:38:04 PM
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]
>> 
>> .

0
mrlewis52
4/26/2010 4:39:15 PM
Reply:

Similar Artilces:

How to create a looking based on 3 fields
Hi, Can someone point me in the right direction with what I want to do: I know how to use data validation to create drop downs which is phase 1. How do I cause a result to occur based on what was chosen e.g. Col A = (user chooses ABC) Col B = 80 (because ABC was choosen) Sheet 2 will have the 'list' to choose from, so it's got all the values in a Column for the user to choose from. E.g. Col A = ABC Col B = $90 Let me know if I haven't explained what I want to archive correctly. It sounds like you can use =vlookup() or =index(match()) to retrieve the assocated value. ...

Automatic Date in Word Document
I need to create a document that I will be sent as an attachment to an email. I want the document to automatically reflect the date the document was sent. I do want to open the document itself. How do I do this? Thank you. -- valerie The only dates that are available to you are 1. A date that you manually enter into the document 2. The date on which the document was created (by use of the { CREATEDATE } field 3. The date that the document was last saved (by use of the { SAVEDATE } field 4. The date upon which the document was printed ( by use of the { PRINTDATE...

Search last 4 digits in an (account number)
I store bank accunt and credit card numbers in various Contact Notes. For example, a credit card # as xxxxxxxxxxxx1234. Outlook contact search would not find this contact if I enter the search for "1234". Is there a way to do this in Outlook 2007? TIA PS - this search works fine in Outlook Mobile. outlook searches from the beginning - so it will only work if you use xxxxxxxxxxxx 1234. Other search tools (like google desktop) may find it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: htt...

Maintaing position of graphics withing a text area when text boxes are linked.
Using Publisher 2003. Making a catalog. A text box will cover a category. Within this category are several different product. Say 4 product. The decription of these four product can run into several pages. So I link these text boxes to make my life easier. One or more paragraphs are devoted to each product. Each product description includes an inserted graphic of the product beside the first paragraph of each product description. Later when I add or change text, the text flows from one text box to another. nice! however the graphics do not follow. So I end up moving each graphic ind...

Look for: Search In => Choose folders=> Search subfolders not working
When I search for an old email using Outlook's Look for: box and specify which folders to search in via the "Choose folders=>Search subfolders" checking a folder does not actually cause any subfolders to be checked. Unless there is some other setting and/or option hidden away somewhere, the "Search subfolders" check box is not working. I must manually check all the desired subfolders. What am I doing wrong? -- Regards, Peter Sale Santa Monica, CA USA To email me, just pull 'my-leg.' ...

NOW() DATE()
I have Form that populates a Table. On the Form, I have a Text Box with a default value of Date(). I want to change this default value to NOW() so that I can also see the time in my table. I don't want, however, users to see the time on the Form (only the date). Is there a way to display Date() on the Form and populate Now() into the table. I realize that I could create two controls and then hide one on the Form; I'd like, however, to do this in the same Text Box. Any suggestions? alex Set the default value of the textbox to Now() and then set the format to Short Date. -- ...

Searching for Newsgroups
I subscribe to a Usenet service for accessing various news groups. After I download all the newsgroups from that server I can not find a way to search for the newsgroups I have interest in. When I put something like "RV" in the search box on the top right corner, it searches all of my Emails rather than just the list of groups that had "RV" in the title. Help tells me that there is a " Display newsgroups containing" box but for the life of me I can't find it. Any thoughts out there? I am running Entourage 12.2.3 Dunc Click the bar in th...

Search & replace in formulae
Is it possible to do a search and replace which includes the cell formula contents ? I have a workbook that has cells which link to the contents of a cell in another workbook and I'd like to know if the naming has to remain constant or if I can replace a part of the name periodically. ie each year. thanks "Iain Rhodes" <iain@pricejam.com> wrote in message news:8ec201c49682$858c5d20$a501280a@phx.gbl... > Is it possible to do a search and replace which includes > the cell formula contents ? I have a workbook that has > cells which link to the contents of a cel...

SOP Date Required rolldown?
Is there any utility or way to easily update the date required field for all the items on a sales order? My scenario is that I'll create 10 diff sales orders for a big sale to 1 customer (for different systems shipping at different times ). Now the shipping date is delayed by 2 months (due to construction delays, etc). In order to change the date required on the sales orders, I need to change it on every line of every sales order - changing it at the header-level of each document does not roll it down to all the line items. I can do this change through SQL, but that's not i...

Search engine optimization
Hello all! My question today regarding this issue is: I have noticed some sites have the following on their sites "Source". meta name= "robots" content="Index, follow" meta name= "revisit-after" content="15 days" or (30 days) What does this function accomplish as far as search engines go? and will it really imporve your results ( rankings) Like for instance "robots" "index, all" what is the difference between this and saying "robots" "index, follow" I'm a bit confused about this. Can someone ...

Change color on date field in form for re-certification
Hello. I have two different date fields on a form, but I would like them to change color depending on how close to the re-certification date it is whenever I open the form or open a report showing the same info. On each of the date fields, I would like to have them green for anything over a month away, yellow for anytime within a month, and red for anything expired. One date field [PTDate] needs re-certified every 6 months, and the other date field [WeaponDate] needs re-certified every 12 months. I've been struggling with this for several days now and haven't been able t...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Can I search identical fields from multiple tables?
I have a database to log details about staff in my school. I have a tables for staff details, courses, absence, educational visits etc. In each table I have a date field to show when the member of staff is not present. I want to produce a query to search all tables by entering a date - e.g. to bring back all results for staff who are not in school on a certiain day because they are either ill, on a course or on an educational visit. The query will work by entering a date once - it will then search all the other tables and bring back the results. Is this possible? Do I restructure? ...

How To Import Blank Fields into Picklist Fields
Hi All, I'm importing Contacts using the simple method (tools- import-contacts-from file...). Everything is going well except for little thing: I can't seem to import blank data values as blanks. If I map the blank data value to "Default Value", then "Default Value" is literally imported (without the quotes). Do I need to use the Data Migration Framework to get this functionality? I don't want to go there, because I'm 96% completed doing it the simple way. Any suggestions? Thanks, Ian Hi Ian Had the same problem A simple workaround that worked...

What does outlook search when you search Contacts ?
When you do a search in oulook for contacts, what is the criteria that outlook searches? Or I guess how does outlook search? Why is it not consistant? The reason I am asking is that we have our customers contacts in public folders. In our customers we have 6 contacts for XYZ Foods. If you search in outlook for just "XYZ" it only comes back with 4 of the six. If you do a search for "XYZ Foods" it returns with all 6. And if you just entered "Foods" no contacts were found. And we actually have a few companies in our contacts who's names are a two part name ending...

Command Button To Delete Text In Single Field In Record
Hello All, I want to be able to do is open a particular form, which based on a table. "Enquirys" form is called "Enquiry_Date_Reset" On the form i want to scroll to a particular record and then press a button which will clear the date which is stored in the [Date Sent] field for that particular record set. (Not the whole record) is this possible? Regards Dan On Fri, 03 Aug 2007 09:08:56 -0700, dan.cawthorne@gmail.com wrote: > Hello All, > > I want to be able to do is open a particular form, which based on a > table. "Enquirys" > > form ...

Sorting numbers acts like text sort
We have a worksheet (Excel 2000) that was e-mailed to us. When we open it, it says there are macros, which there aren't. No matter if we say "Disable" or "Enable" macros, all cells are formatted to "General", and when sorting, the numbers 10000, 7102, 1169 sort as: 10000, 1169, 7102. We can try to format as number, but to no avail. Sorting still isn't right. Have seen this many times before, but don't know how to resolve. Can you help? Thanks. Never mind. Sorry to waste your time. Saw two suggestions below on this page. Missed them the first ...

excel VBA
How would i search a range of a whole column instead of specific cells like .Range("a1:a300")? --- Message posted from http://www.ExcelForum.com/ ..range("a:a") ..columns(1) ..range("a1").entirecolumn are a few ways. "Zygoid <" wrote: > > How would i search a range of a whole column instead of specific cells > like .Range("a1:a300")? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ahhh! Thanks!! I was trying .Column(1) i see i neede an "s" in there Thanks a...

Printing RTF text using a hidden CRichEditCtrl
Hi there, is it possible to print text using a hidden CRichEditCtrl? I was using some code from the article: http://www.codeproject.com/printing/richeditprint.asp In my application I do not always display the text. Sometimes I just need to put toghether some rtf text using template files and print the text. Here is the code I'm trying to make to work. I'm checking return values and cannot find any problems, except that the printer doesn't do anything. void CMainFrame::OnClickherePrint() { // Read In text. std::ifstream in( ".\\template.rtf" ); std::string st...

Multiple fields using to search
Hi, Something i've been batteling for weeks with and theres probably such a simple answer to it and i just cant see it: Riiiight, i have this form (lets call it frmClientSearch) On this form i have a 2 list box thinghys; One called lstEmployee which is populated with Employee names and the other lstCity which is populated with City Names. Then i have a command button (cmdSearch) which, once click and values selected in both the lst boxes, should return only the values that are equal to both the lst boxes in a new form called frmSearchResult. (Hope i make sense) So... I know how ...

How to hide and un-hide fields upon condition
I have a text box on a form and next to it I have another text box that I want to hide unless there is data in the first text box. So, if no information is entered in text box 1 then hide text box 2 and vice versa. Is this easy to do? In the form's Current event, put: Me.Textbox2.Visible = Len(Me.Textbox1 & vbNullString) > 0 Put the same code in the AfterUpdate event of Textbox1. If you want the content of Textbox2 to be erased if the content of Textbox1 is, use Private Sub Textbox1_AfterUpdate() If Len(Me.Textbox1 & vbNullString) = 0 Then Me.Textbox2 = Null M...

Inbox displays incorrect date
The email header contains the correct date time: Wed, 23, Mar 2005 09:59:16 -0800 but the Outlook Inbox shows Wed 3/1/2023 9:59 AM. Note it has replaced the last two year digits with the date digits and made the date the first of the month. This only occurs from one sender and we are the only recipient that has this problem. Any suggestions? -- Jim Goeben Web and Database Manager Kittitas County Jim Goeben <JimGoeben@discussions.microsoft.com> wrote: > The email header contains the correct date time: Wed, 23, Mar 2005 > 09:59:16 -0800 but the Outlook Inbox shows Wed 3/1/2023...

Inconsistency with date format for downloaded transaction
I have been having trouble with downloading statements from bank website. The files is in QIF file however, the date format they use is MM/DD/YYYY format however Money 2003 is expecting DD/MM/YYYY format so it messes up the transactions imported. Anyone has any idea to fix this problem? Thanks In microsoft.public.money, Yakster wrote: >I have been having trouble with downloading statements >from bank website. The files is in QIF file however, the >date format they use is MM/DD/YYYY format however Money >2003 is expecting DD/MM/YYYY format so it messes up the >trans...

Search, Search, Thanks
In the last couple of weeks I have been fighting with different ways o synching my laptop and my home pc. I have ended up with numerou duplicate entries, duplicate address books and duplicate contact lists ALL of my questions/problems I was able to resolve simply by searchin through the forums. Chances are if you have a problem you're not th first. There are some really good people on these forums that know alo more about Outlook than I do. Just my .02 Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages dir...

Export text with current date
hi, I want to export a few queries to text files. I am current using "transfer text" option through macros. If we provide the file name and the path the files are exported to the path. How do we add the current date at the end of each file. eg. abc is the query name & i want the query to be exported as data_25122007. Currently i am able to get the file name as data_. the date should be dynamic (ie) should give the current date at the end of the file. Any ideas, Pls help. Hi, also from Australia is an answer for you strFileName = "queryName" & "_" ...