What I need to be able to do search by DATE on a DATETIME field.
Main Topics
Browse All TopicsI have a form. In that form there is a pulldown box that a user will select a record, and then they will enter a date into a textbox. This information will be passed to a query that will return records matchin the criteria selected from the pulldown AND the DATE entered, regardless of the time of day in the record.
However, in my form, I am getting a type missmatch due to trying to match a date entry to a datetime record.
How can I search based on a DATE entry to a Datetime record in an access form? The other half of the search works fine. But this is kind of perplexing.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Looking for an update on this. I am trying to do this from a FORM, not in an SQL statement.
So, to re-state the question.
I need to pass 2 values to a report. An item selected from a combo box, and a date that will need to match a datetime row. The user will only enter a date, with out the time portion. So, I need to find a way, IN VB, not in SQL statement, that I can match all records on that specific date, regardless of the time of day.
Can anybody help?
The date field can be called DateOfService.
stLinkCriteria = "[user]='" & Me![Combo64] & "' And " & DateValue([Note_date]) & " = #" & Me!ReportDate &"#"
In the immediate pane of the VB window I type thefollowing and change the above to:
var1="Ray"
mydate=#2004-05-06#
stLinkCriteria = "[user]='" & var1 & "' And " & DateValue(now()) & " = #" & mydate & "#"
? stLinkCriteria
[user] = 'Ray' And 2005-07-16 = #2004-05-06#
Of course that is false and would not return any records, but it is to say there is an error somewhere in your string. Put a break on the line causing the error and when it gives the error, examine all the variables before the line as well as those in the line. Something is screwy somewhere.
Because Me! has no meaning outside the object that called it, in order to view the criteria string in the Immediate Pane, I just created a variable var and assigned my name to it as well as the ReportDate. It is to say that I had to prove to myself the concatination was correct in this string:
stLinkCriteria = "[user]='" & Me![Combo64] & "' And " & DateValue([Note_date]) & " = #" & Me!ReportDate &"#"
So if you define stLinkCriteria in your form, and the controls Combo64 and ReportDate contain valid data, then the above criteria should work correctly.
I just came over from your new post and scanned the above thread.
One thing that may be confusing you is there is no such thing as a date field in Access. There are only Date/time fields. A date/time field is a double indicating the length of time accurate to 15 digits since 12/30/1899 12:00:00 AM (try ?format(0,"mm/dd/yyyy hh:nn:ss AM/PM") in the immediate window). You indicate a number is a date to Access by placing the pound sign: # around it as in: #38550.6367592593# = #7/17/2005 3:16:56 PM#.
GRayL is very sharp especially with queries, so if he has not been able to help you, there must be a communication problem. The only way I can see to get past that is for youto post the entire query you have that is not working as perennial suggested. In the query builder, goto view>SQL view. Copy and paste that here. GRayL, I or someone else should then be able to rewrite the query for you to paste back in.
here is SQL created by the Query Builder tool, that does in fact work, in Access 2000:
SELECT DateValue([fldDate]) AS Expr1
FROM tblWorksheet
WHERE (((DateValue([fldDate]))>#
This will retrieve ALL records after the supplied date.
what thenelson told you about Date/Time fields is PRECISELY correct, as far as it goes.
Dates (and times) are stored, in Access, as a Floating point decimal number. The Integer part of the number (to the LEFT of the decimal point), is the COUNT of the number of days since the base date of Dec 30, 1899. The fractional part of the number (to the RIGHT of the decimal point, is the time, measured in seconds since midnight, as a fraction of 1 day (1 day = 86400 seconds), so that 12:00 NOON would be represented as .5
and 3:16:56 PM on ANY day of any Year, would be represented as .6367592593
The DateValue function , which can be used in SQL queries, simply returns the integer part of the Date/Time field, and then formats that value as a Date (MM/DD/YYYY).
AW
I have no experience with accessing data from SQL Server but what happens if you use the following?:
stLinkCriteria = "[user]='" & Me![Combo64] & "' And "[Note_date] >= #" & Format(Me!ReportDate, "yyyy/mm/dd") & "# And [Note_date] < #" & Format(DateAdd("d", 1, Me!ReportDate), "yyyy/mm/dd") & "#"
Steve
I thought that DateValue and Format were ACCESS VB Functions, and not SQL functions. Is this correct?
Also, perhaps there is a completely different way of doing this.
All I need to be able to do is have a form that a user selects a user's name from a combo box, and a date entered in a text box and pass those values to an existing SQL query. WHat about having the form generate the entire query. It's not a complicated query. I have pasted the query below.
SELECT dbo.Referrallocal.Account_
dbo.Referrallocal.LienASC,
dbo.Referrallocal.Units, dbo.ReferralNotes.Note, dbo.ReferringPhysician.DrL
dbo.ReferringPhysician.Gro
FROM dbo.Referrallocal INNER JOIN
dbo.[Patient Demographics] ON dbo.Referrallocal.Account_
dbo.ReferralNotes ON dbo.[Patient Demographics].Account_ID = dbo.ReferralNotes.Account_
dbo.Referrallocal.Ref# = dbo.ReferralNotes.Referral
dbo.ReferringPhysician ON dbo.Referrallocal.Referrin
I am getting a type mismatch from "dbo.Referrallocal.Ref#" because the query is seeing the # and is expecting a date. I corrected all the field names with # in it by placing them in brackets: dbo.Referrallocal.[Ref#]. But hen I get a syntax problem in the INNER JOIN statement which I cannot find because I don't have the tables and fields. If you place that SQL string in your query builder, can you switch to datasheet view? If not then the problem all along is we all were trying to add a WHERE statement to a query that doesn't work to start with.
The query works perfectly. I have been using it for some time now. We are trying to add more functionality. Currently, the where statement is created by the form to print the report used by the query for the current date.
Again, let me state this as clearly as I can. If I simply pass the value selected in combo64 to the query, it works flawlesly.
Good! I'm glad we are starting with a query that works and can build from that!
So since several people have been trying to help you without success, I would rather not suggest a WHERE clause to add to the query that may add to the frustration I see in your writting. Since "If I simply pass the value selected in combo64 to the query, it works flawlesly" could you show me the entire SQL statement with the combo64 value passed to it. I am trying to get as much information as possible to increase the odds that the answer will work.
The QUERY only contains the where statement.
WHERE dbo.referrallocal.Account_
Then the form, when working, passes this code to the form
stLinkCriteria = "[user]=" & "'" & Me![Combo64]
Again, the above line, on it's on, works perfect.
When you add the AND clause trying to limit to a specific date entered, all *&^%$@* hits the fan, resulting in the errors I have listed at the beginning half of this thread.
I wonder if there is a record set for the number of entries in a Question in Experts Exchange.
Patrick.
What is the name of the form [Combo64] and [ReportDate] (I assume from reading above [ReportDate] has the date) ?
Also I am assuming you want to compare:
dbo.ReferralNotes.Note_dat
Is that correct.
>>>all *&^%$@* hits the fan,
I know how you feel, see:
http://www.experts-exchang
The form is called [Main Menu] (Not my switch board, and I know, Space should not be there, but it is, and I don't want to go through the headache of the change, it's used all over the place.)
And yes, I am trying to match dbo.referralnotes.note_dat
Just as a reminder I am trying to make this code work....
stLinkCriteria = "[user]=" & Me![Combo64] & "'" And [Note_date] = #" & DateValue(Me!ReportDate) & "#" <---- Me being [Main Menu]
Again, I appreciate all of the work you guys have been putting into this one.
I am also going to try out a function I found in an oreilly book (Access Cookbook). Supposedly it will format your code for you depending on your datatype.
Then how does stLinkCriteria get "passed to a query"? And by "a query" do you mean the query above? at:
http://www.experts-exchang
Code for the "On Click" event of the button on the form.
Private Sub Dialy_Auth_Report_Click()
On Error GoTo Err_Dialy_Auth_Report_Clic
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'"
stDocName = "DailyAuthLog"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_Dialy_Auth_Report_Cli
Exit Sub
Err_Dialy_Auth_Report_Clic
MsgBox Err.Description
Resume Exit_Dialy_Auth_Report_Cli
End Sub
The above code prints a report that prints all of the notes for an individual user. Currently the way it works is that at the end of the day, users print this report. If I add the following to the where statement
WHERE (dbo.ReferralNotes.Note_da
Then the report displays all notes for the current date. HOWEVER, this causes a problem when the user works until 5:00pm one day, and the next day, the only work until 4:30. It will print notes for the previous day at 4:30 until 4:30 on the current day. Thus this entire annoying thread.
If the above code works then we would expect you will get the same result if you replace:
stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'"
with:
stLinkCriteria = "[user]='" & Me![Combo64] & "' And [Note_date] > #1900/01/01#"
Note: this is just a test to see if we can add a very simple date criterion without using any functions.
Steve
Hi thenelson and PatrickSalter,
When dealing with date strings, I have found it best to use 'yyyy/mm/dd' because it is interpreted unambigously by Access. I used to convert all my dates to 'mm/dd/yyyy' because that is apparently what Access works in by default but found that this didn't always give me consistent results on all computers. This lead me to turn to using 'yyyy/mm/dd' and since then have had no problems with inconsistent date interpretation.
Steve
AW post: http:Q_21448810.html#14463
Not quite. The DateValue function returns the date, formatted as per your system setting.
>How can I search based on a DATE entry to a Datetime record in an access form?
Depending on how you intend to display the results of the query. Also assuming the value retrieved from the combobox is numeric you would use a query something like this:
Select * from yourtable where Yourfield=" & Me,[YourComboBox] & " AND YourDateField=#" & CDate(Me.[YourDateTextBox]
You should play with the date sorting to get what you want:
YourDateField >=#" & CDate(Me.[YourDateTextBox]
YourDateField <=#" & CDate(Me.[YourDateTextBox]
etc.
Ray,
While you were gone, we have established that strLinkCriteria is not being used in a query but in the where condition of an open form command (http:Q_21448810.html#1446
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Also that this works:
stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'"
Then Steve suggested adding a small step (http:Q_21448810.html#1446
stLinkCriteria = "[user]='" & Me![Combo64] & "' And [Note_date] > #1900/01/01#"
If Patrick states that works, then moving up to your:
strLinkCriteria = "user='" & Me![Combo64] & "' And " & DateValue([Note_date]) = " & CDate(Me!ReportDate))
would make sense.
Everyone jumping in with suggestions has not been helping Patrick so I will leave this in your capable hands.
Nelson
So, since this is starting to cause me ulcers as well as inspire me to drink, I thought I would do a test.
I created a new table, in the same database. It has 3 field, including one datetime field.
With the following SQL command:
SELECT KeyColumn, Header, Calendartime
FROM dbo.Datetesttable
WHERE Calendartime = #07/15/05#
When I try to verify the SQL syntax I get this error:
ADO error: Line 1: Incorrect syntax near '#'.
Statement(s) could not be prepared.
Defered prepare could not be completed.
This makes me think that something is wrong with the database it self.
Or perhaps I have been at this for so long, I can't see the one stupid thing I am doing wrong.
ALL input is greatly appreciated.
What you posted caused a syntax error at the quote on the 2nd Cdate.
I changed it to this...
stLinkCriteria = "[user]=" & Me![Combo64] & "'" And CDate([Note_date]) = CDate("'" & DateValue(Me!ReportDate) & "'")
and got this error: dataase can't find the field '|' referred to in your expression.
I am getting to the point of considering abandoning this problem because it seems like there is something else going on with the SQL server it self.
However, given that I have been at this for so long, I guess I will work on this thing until I get it fixed.
Hi Patrick,
As I said before I am not experienced with SQL server but it seems that date criteteria are not delimited by # In SQL Server SQL.
See the following for example:
http://www.experts-exchang
Steve
Patrick: This stuff is getting pretty off the wall. Does this work?
stLinkCriteria = "user=" & Me![Combo64] & "'"
If OK then add:
stLinkCriteria = "user=" & Me![Combo64] & "' And DateValue([Note_date]) = #2005-06-07#" <-- use the date format of your system
If OK then chg the date to:
stLinkCriteria = "user=" & Me![Combo64] & "' And DateValue([Note_date]) = #" & Me!ReportDate & "#"
This worked in my db.
I think I found your problem. Is dbo the name of the database you are working in. If so, get rid of the reference in the query. Instead of:
SELECT KeyColumn, Header, Calendartime
FROM dbo.Datetesttable
WHERE Calendartime = #07/15/05#
use:
SELECT KeyColumn, Header, Calendartime
FROM Datetesttable
WHERE Calendartime = #07/15/05#
If that works, you know what you need to do with the query at:
http://www.experts-exchang
No. dbo is not the name of the database I am working on.
I did remove the dbo prefix from the table name, and it changed nothing.
HOWEVER, I am really starting to think there is a problem with SQL server it self.
Can anybody think of why this code results in an error?
SELECT KeyColumn, Header, Calendartime
FROM Datetesttable
WHERE Calendartime = #07/15/05#
The error I get when I try to verify the SQL syntax is...
"ADO error: Line 1: Incorrect syntax near '#'.
Statement(s) could not be prepared.
Defered prepare could not be completed. "
That should work, right? So, what could possibly cause this problem????? Bad install of SQL (SQL has been working flawlessly since install 2 years ago), perhaps a bad config?
I have not used SQL server but I did a search in EE on "sql server date field" and found these that may help you:
http://www.experts-exchang
http://www.experts-exchang
http://www.experts-exchang
OK, a LITTLE progress. I decided to try JUST the part of the statement we have been struggling with...
I took a sample record from the table it self and used this stLinkCriteria:
stLinkCriteria = "note_date=" & "'" & "7/15/2005 11:02:52 AM" & "'"
And it works!!!! I get a report with a SINGLE record!!!!!
So, lets simplify a bit. I want to run a report, regardless of the username, for ALL records on date 7/15/2005.
If I truncate the statement to read...
stLinkCriteria = "note_date=" & "'" & "7/15/2005" & "'" <----- Which is to day, I removed the TIME portion of the value passed to the query, which gets us right back to where we were before.
So, how in the name of all things binary, do I pass a value #7/15/2005# and get a report that displays all records entered for the entered date?
Inquiring minds have lost their minds. (If I don't lighten this up, I will lose my mind)
Hey, I have an idea, I would be more then happy to take a piece of code that did this...
If the user enters 07/15/05
It passes 2 values to the query. A beginning and ending datetime. Beginning being 07/15/2005 00:00:00AM and ending at 07/15/2005 23:59:59pm and giving me all values involved. In fact, I would even be happy if the user had to enter 2 seperate dates, which would give them the flixibility of running the report for say, a week, month, quarter or year, etc....
So, lets assume that is what I want.
The user will have to enter 2 seperate dates, a starting date and an ending date. The form will add the 0hour and 24 hour times to those dattes and run the query. How hard could that be?
Hi Patrick,
I feel that you need to get help from someone who understands SQL Server. I would suggest creating another pointer question that clearly lets everyone know that you are dealing with data stored in SQL server.
I have searched EE and beyond but cannot find anything that looks like it will completely answer your needs but the following links may assist you.
http://www.experts-exchang
http://www.experts-exchang
Steve
>>>stLinkCriteria = "note_date=" & "'" & "7/15/2005 11:02:52 AM" & "'"
If that works then one of these should work:
stLinkCriteria = DateValue(note_date) & "=" & "'" & "7/15/2005" & "'"
stLinkCriteria = Cstr(DateValue(note_date))
stLinkCriteria = Format$(note_date,"mm/dd/y
stLinkCriteria = Format$(note_date,"shordat
acperkins,
So when setting up criteria for an Access report, could we expect the following syntax for stLinkCriteria to work (assuming we wanted to return all records for 7/15/2005 (m/dd/yyyy) and that the Note_date field contains dates and times e.g. 2005-07-15 07:30:00
Private Sub Dialy_Auth_Report_Click()
On Error GoTo Err_Dialy_Auth_Report_Clic
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[user]='" & Me![Combo64] & "' And [Note_date] >= '2005-07-15' nd [Note_date] < '2005-07-16'"
stDocName = "DailyAuthLog"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_Dialy_Auth_Report_Cli
Exit Sub
Err_Dialy_Auth_Report_Clic
MsgBox Err.Description
Resume Exit_Dialy_Auth_Report_Cli
End Sub
Steve
There is a small typo, it should be:
stLinkCriteria = "[user]='" & Me![Combo64] & "' And [Note_date] >= '2005-07-15' And [Note_date] < '2005-07-16'"
Or more simply:
stLinkCriteria = "[user]='" & Me!Combo64 & "' And Note_date between '2005-07-15' And '2005-07-16 23:25:59'"
However, this is making the assumption the backend is SQL Server and with the caveat that I can barely spell MS Access :)
I feel like I need to further clarify the problem....
My form has 2 pieces of information that are entered by the user. From a combo box, a user name is selected. In a text box a DATE is entered in the format mm/dd/yy.
In the table these records are pulling from the informatoin in the date filed is stored as a DATETIME record, this INCLUDES the time the record was entered in the format "mm/dd/yy hh:mm:ss AM/PM"
All I need to be able to do is this...
Match ALL records based on the 2 pieces of information in a query. That query is then going to a report.
The problem is this...
If I set the form up to match an entire datetime entry, and I enter the full datetime (I.E. 07/15/2005 01:25:11 AM) I can get it to work. However, this is a table that a user will enter a large number of entries on ANY given day, I.E. I need to match the date regardless of time.
To make this simpler, I could force the employees to enter a date range. If they want it for the single day, then they enter 07/15/05 and 07/15/05. If they want it for the week, they would enter 07/11/05 and 07/15/05. I don't mind that change if that is the only way to accomplish what I want. However, this is a problem that has eluded my SQL/Access skills, and I need your help.
ALso, this is SQL server 2000 with sp3 applied and the Access front end is Access 2003.
Well, I have a solution.... I am not going to consider this question closed, because the solution is not perfect... But here it is....
By adding the following where clause:
WHERE (dbo.ReferralNotes.Note_da
The user will now be prompted with 2 popup dialogue boxes. One called Reportdate1 and reportdate2. The user will enter those dates.
So the stLinkCritera statement in VB is now:
stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'"
The user will select the user name from the combo box, and then the 2 dialogue boxes will finish the job....
However, I need a way to add the time 23:24:59 to the @reportdate2 box. Any ideas?
If you use the pop-up parameters method, just get the user to type in the date of the following day for the second parameter. They won't need to specify the time.
Did you try the suggestions by acperkins? If they work, you will be able to achieve your original intention with a few minor adjustments which should also be more user friendly.
Steve
If you can get any of the suggestions by acperkins to work then you can have a form with two textboxes for the user to enter dates. Your code can be modified so that the user need only enter one date in one textbox with the other left blank, if the user only wants to view data for one day, otherwise the user can enter another date in the second textbox to view data for more than one day.
Steve
Using acperkins's and jimpen's suggestion try:
stLinkCriteria = GetLinkCriteria ()
Private Function GetLinkCriteria () As String
GetLinkCriteria = "[user]='" & Me!Combo64 & "' And Note_date between '"
GetLinkCriteria =GetLinkCriteria & Format$(Me!ReportDate,"yyy
GetLinkCriteria =GetLinkCriteria & Format$(Me!ReportDate,"yyy
End Function
If you put a breakpoint in the form code somewhere, during the break, you can put:
?GetLinkCriteria
in the immediate window to make sure GetLinkCriteria returns the string correctly.
I thought of something when the cat, who is in heat, woke me up at 4:45AM this morning. First kill the cat. Second a good view of killing the cat.
That brings me to views. Can you create a view of the SQL Table and run off that instead of the table. All a view is is a SQL Query that presents as a table. Your statement would be like
Create View ReferralNotes_VW
AS
SELECT Field1, Field2, ..., Cast(Convert(Varchar,Note_
FROM dbo.ReferralNotes
This will set the note_date to be 2005-07-15 00:00:00.000.
Plus you also need to consider record locking if you have another app that uses the DB and you just report off it.
http://www.experts-exchang
jimpen,
>>SELECT Field1, Field2, ..., Cast(Convert(Varchar,Note_
FROM dbo.ReferralNotes
This will set the note_date to be 2005-07-15 00:00:00.000.<<
Actually not quite. Style 103 is the British/French standard of dd/mm/yyyy, so you will actaully get an error:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"
I think you mean something like this:
SELECT Field1, Field2, ..., CAST(CONVERT(char(10), Note_date, 112) as DateTime) As Note_Date_Trunced, Field4,..........
FROM dbo.ReferralNotes
AC you're right on the 112. I said this came at 4:45AM. I didn't look up the conversion code
I think you mean something like this:
SELECT Field1, Field2, ..., CAST(CONVERT(char(10), Note_date, 112) as DateTime) As Note_Date_Trunced, Field4,..........
FROM dbo.ReferralNotes
I was reading a few months back on another SQL forum web-site, a guy got burned by doing it the way you're suggesting below.
SELECT Field1, Field2, ..., CAST(CAST(Note_date as char(11)) as DateTime) As Note_Date_Trunced, Field4,..........
FROM dbo.ReferralNotes
I took another look at this, CAST may be the solution.
Using
CAST(CONVERT(char(10), dbo.ReferralNotes.Note_dat
And with out passing data to it does indeed give me the DATE FIELD on it's own. Now, I just need to pass data to that field, and see if it works!!!!!! If this is the case, JIMPEN is THE MAN!
Back in a bit to check this out further.
Business Accounts
Answer for Membership
by: perennialPosted on 2005-06-06 at 16:36:41ID: 14158026
What does your code looks like? Check your date in the table and in the criteria, make sure that they both are date field; if not then you will have to convert them. It will be better if you could post your code.
perennial