Need to enter a Date in a form and match that date to all records based on a DATETIME field ignoring the TIME.

What I need to be able to do search by DATE on a DATETIME field.  

In the table, you need to go to design view and check to see if it is a date format.

Let me know if you need more help.

perennial

Again, let me clarify.   I need to search a DATETIME record, BY DATE ONLY.   I.E.  I need to see all entries on a specific date, based on a datetime record.   I need to simply IGNORE the time portion of the datetime.   I need to do this from a form, and the user needs to be able to specify the date.  

I need to keep the timestamp.   It was set up as a datetime format for a reason, and changing the format would only be the beginning of a LOT of work to correct.  

OK, in this case in your query format your date like the following then put in the criteria:

MyDate: Format([DatField],'mm/dd/yy')

This will give you just the date portion of your date field.

Make sure that in your form you format your field as a short date.

perennial

Would I add this to the select statement?  

As in...
SELECT    dbo.ReferralNotes.Note_date,

Change to

SELECT   Format([dbo.ReferralNotes.Note_date],'mm/dd/yy')

Correct?

Yes, something like this:


SELECT   Format([dbo.ReferralNotes.Note_date],'mm/dd/yy') as MyDateField;

Getting an error that says "Format is not a recognized function name. "

Odd....

Post your whole sql statement.

Lets assume I am building from scratch....

SELECT   Format([dbo.ReferralNotes.Note_date],'mm/dd/yy') as Date

Just that alone gives the same error.

Date is a reserved word.

Try:

 SELECT   Cdate(Format([dbo.ReferralNotes.Note_date],'mm/dd/yy') as MyDate)

What ever your datetime field is, do a search on datevalue(myDTfield).  This will strip the timevalue from the myDTfield.

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.

Perhaps this will help as well.  

  stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'" And "[Note_date] =" & Me!ReportDate

This results in a type mismatch error.  

Note_date is a datetime datatype.  

Try this.  As I said before DateValue() strips off the time portion of a datetime value and you need date delimiters around ReportDate

stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'" And "DateValue([Note_date]) =#" & Me!ReportDate &"#"

Still getting type mismatch....

Still scratching head.

Quotes within strings can be tricky.  Try this:

stLinkCriteria = "[user]='" & Me![Combo64] & "' And " & DateValue([Note_date]) =#" & Me!ReportDate &"#"

I am assuming, GrayL, that you may have just missed an extra quote....
stLinkCriteria = "[user]='" & Me![Combo64] & "' And " & DateValue([Note_date]) ="#" & Me!ReportDate &"#"

After adding the additional quote, I get another error...  
cannot find the field '|' referred to in your expression.

Close:

Change:

stLinkCriteria = "[user]='" & Me![Combo64] & "' And " & DateValue([Note_date]) =#" & Me!ReportDate &"#"

To:

stLinkCriteria = "[user]='" & Me![Combo64] & "' And " & DateValue([Note_date]) & " = #" & Me!ReportDate &"#"

Same error....   GrayL, I appreciate the quick responses.   I have no idea why this is not working.

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.

Yep, you lost me there.  

Can you re-explain that one.  

Again, the help is greatly appreciated.   I am confused as to why access makes this so complicated.  

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.

unfortunately that is not working.   I am still getting error "cannot find the field '|' referred to in your expression."

I have copied the string directly from your post.  

Try:

stLinkCriteria = "[user]='" & Me![Combo64] & "' And DateValue([Note_date]) = #" & Me!ReportDate &"#"

Steve

OK, for a moment, that looked like it was actually trying to process something, then returned the error

'DateValue' is not a recognized function name.  

I am working with Access 2003.  

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.

Pat:
Something doesn't seem to jibe here.  
In a query window, can you pull up your table with [note_date] and apply datevalue to that?

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]))>#3/1/2004#));

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

thenelson  

This is access 2003 front end for SQL server.   The datatype I am using is DATETIME, which we are trying to convert to just display the date.  


Let me try Jerryb30's suggestion and apply datevalue to my notedate field.  

Well, perhaps this is my issue.   DateValue is not being recognized as a function....

Hrm.   This is SQL Server 2000 with SP3 applied.  

Thoughts?

In your WHERE statement try adding something like this:

(([YourTable].fldDate) between Me.Note_date and Me.Note_date)


Nathan

OR
datediff("d", [note_date], me.reportdate) = 0?

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

Still no success.  I have tried every suggestion here.   ANy other suggestions?

What error messages are you getting?

Steve

A large variety of them.   Scroll through this post, and you will a variety, depending on what I have been trying.

Sorry I have to go out now but it might help others to help if you can provide the exact error messages  for each of the last 3 suggestions.

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_ID, dbo.Referrallocal.Ref#, dbo.ReferralNotes.Refnote#, dbo.ReferralNotes.Note_date, dbo.ReferralNotes.[User],
                      dbo.Referrallocal.LienASC, dbo.Referrallocal.Surgery_Center_Specified, dbo.ReferralNotes.Outcome, dbo.ReferralNotes.FollowupDate,
                      dbo.Referrallocal.Units, dbo.ReferralNotes.Note, dbo.ReferringPhysician.DrLastName, dbo.ReferringPhysician.DrFirstName,
                      dbo.ReferringPhysician.GroupName, dbo.ReferringPhysician.City
FROM         dbo.Referrallocal INNER JOIN
                      dbo.[Patient Demographics] ON dbo.Referrallocal.Account_ID = dbo.[Patient Demographics].Account_ID INNER JOIN
                      dbo.ReferralNotes ON dbo.[Patient Demographics].Account_ID = dbo.ReferralNotes.Account_ID AND
                      dbo.Referrallocal.Ref# = dbo.ReferralNotes.Referral# INNER JOIN
                      dbo.ReferringPhysician ON dbo.Referrallocal.ReferringMD = dbo.ReferringPhysician.ID

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.  

Also, this is a SQL backend with Access front end, and perhaps that is why you are getting errors, no SQL server to match this to.    

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_ID <> 17  <---- I use this to filter our a dummy test account I use.  

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_date = [ReportDate]
Is that correct.

>>>all *&^%$@* hits the fan,
I know how you feel, see:
http://www.experts-exchange.com/Databases/MS_Access/Q_21438110.html

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_date to [reportdate]  

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.  

Where does "stLinkCriteria" come from?

I'm not sure i understand what you mean.  

stLinkCriteria is defined in the form [Main Menu]

Presumably stLinkCriteria is a criteria string that is being specified in some VBA code. Can you provide all the code for this procedure?

Steve

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_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
   
   
   stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'"
    stDocName = "DailyAuthLog"

    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 
Exit_Dialy_Auth_Report_Click:
    Exit Sub

Err_Dialy_Auth_Report_Click:
    MsgBox Err.Description
    Resume Exit_Dialy_Auth_Report_Click
   
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_date > GETDATE() - 1)

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

Sounds reasonable.   Let me try....

Patrick,
Depending on what country you are in, you would use:
tLinkCriteria = "[user]='" & Me![Combo64] & "' And [Note_date] > #1900/01/01#"
or
stLinkCriteria = "[user]='" & Me![Combo64] & "' And [Note_date] > #01/01/1900#"

Also do you have a control (textbox, listbox or combobox) named "Note_date" in DailyAuthLog report?

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

Have you tried:
stLinkCriteria = "[user]=" & Me![Combo64] & "'" And Cdate([Note_date]) = CDate(" & DateValue(Me!ReportDate) & ")"  <---- Me being [Main Menu]

AW post: http:Q_21448810.html#14463021, he said: "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)."

Not quite.  The DateValue function returns the date,  formatted as per your system setting.

Since yesterday we seem to have lost the notion that the Note_Date field is datetime and needs to be just date.  

Does this work?

stLinkCriteria = "user='" & Me![Combo64] & "' And " & DateValue([Note_date]) = " & CDate(Me!ReportDate))

>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#14463575):
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Also that this works:
stLinkCriteria = "[user]=" & "'" & Me![Combo64] & "'"
Then Steve suggested adding a small step (http:Q_21448810.html#14463654):
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.  

Have you tried:
stLinkCriteria = "[user]=" & Me![Combo64] & "'" And Cdate([Note_date]) = CDate('" & DateValue(Me!ReportDate) & "')"  

Note the single Quote marks.

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-exchange.com/Databases/Q_21315932.html?query=date+criteria++sql+server+&clearTAFilter=true

Steve

Jimpen

Same syntax error as before.  

stephen perrett
You may be on to something there.   I am going to try converting to a simple date and try my luck with that.

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-exchange.com/Databases/MS_Access/Q_21448810.html#14463177

Disrequard my last post -- false alarm.

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-exchange.com/Databases/MS_Access/Q_20247968.html?query=sql+server+date&topics=39#6712460
http://www.experts-exchange.com/Databases/MS_Access/Q_10043743.html?query=sql+server+date+field&topics=39#1971213
http://www.experts-exchange.com/Databases/MS_Access/Q_20238887.html?query=sql+server+date+field&topics=39#6648659

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-exchange.com/Databases/MS_Access/Q_20508212.html?

http://www.experts-exchange.com/Databases/MS_Access/Q_20099606.html?

Steve

Try this:

stLinkCriteria = "Format(LEFT( note_date,10),'MM/DD/YYYY') = " & "'" & "7/15/2005" & "'"

But just for grins, is there anyway to do a debug.print on the note_date when you open the form or report?

>>>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)) & "=" & "'" & "7/15/2005" & "'"
stLinkCriteria = Format$(note_date,"mm/dd/yyyy") & "=" & "'" & "7/15/2005" & "'"
stLinkCriteria = Format$(note_date,"shordate") & "=" & "'" & "7/15/2005" & "'"

typo, that should be:
stLinkCriteria = Format$(note_date,"shortdate") & "=" & "'" & "7/15/2005" & "'"

and you should be able to combine the appostrophy with the equal sign (but one step at a time):
stLinkCriteria = Format$(note_date,"shortdate") & "='" & "7/15/2005" & "'"

You could also try:
stLinkCriteria = Cstr(Cint(note_date - 0.49)) & "=" & "'" & "7/15/2005" & "'"
The "- 0.49" is because Cint will round up to the next day on afternoon times.  Subtracting 0.49 before Cint will keep it to the same day.

Since you are using SQL Server, you need to use T-SQL, as in change this:
SELECT     KeyColumn, Header, Calendartime
FROM       Datetesttable
WHERE Calendartime = #07/15/05#

To:
SELECT     KeyColumn, Header, Calendartime
FROM       Datetesttable
WHERE Calendartime = '2005-07-05'

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_Click

    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_Click:
    Exit Sub

Err_Dialy_Auth_Report_Click:
    MsgBox Err.Description
    Resume Exit_Dialy_Auth_Report_Click
   
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 :)

And now it is me with the (big) typo.  It should be:

stLinkCriteria = "[user]='" & Me!Combo64 & "' And Note_date between '2005-07-15' And '2005-07-15 23:25:59'"

This is simpler still (but may not be as efficient):
stLinkCriteria = "[user]='" & Me!Combo64 & "' And CONVERT(char(8), Note_date, 112) = '20050715'"

P.S. Sorry for the multiple posts.

acperkins,

Thanks for your help. This should be very helpful to Patrick

Steve

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_date >= @reportdate1) AND (dbo.ReferralNotes.Note_date < @reportdate2)

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

How about a function

Public Function SetBeginDate(InputDate As Date) As Date
    SetBeginDate = CDate(Format(InputDate, "mm/dd/yyyy") & " 00:00:00")
End Function


Public Function SetEndDate(InputDate As Date) As Date
    SetEndDate = CDate(Format(InputDate, "mm/dd/yyyy") & " 23:59:59")
End Function

>>However, I need a way to add the time 23:24:59 to the @reportdate2 box.   Any ideas?<<
That was a typo on my part, it should of course be 23:59:59.

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,"yyyy-mm-dd") & "'And '"
GetLinkCriteria =GetLinkCriteria & Format$(Me!ReportDate,"yyyy-mm-dd 23:59:59") &"'"

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_date,103) as DateTime)  As Note_Date_Trunced, Field4,..........
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-exchange.com/Databases/MS_Access/Q_21398912.html

Well, this is a query that is used solely for a report.   So, while a view is nice, it's not going to format the data in the way we want it.  

But good idea.

You will still have the problem of comparing SQL dates to Access dates.  But the SQL Date will be truncated to the date and the time will always be 00:00:00 (midnight).  That way you don't have to beat on the between  "00:00:00" and " 23:59:59" bit.

Or simpler still:
SELECT Field1, Field2, ..., CAST(CAST(Note_date as char(11)) 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

>>a guy got burned by doing it the way you're suggesting below.<<
I know what you mean.  I hate those dogmatic people :)

But seriously, I don't see any difference between either approach.  On the other hand, I would probably not use either one.

I took another look at this, CAST may be the solution.  

Using
CAST(CONVERT(char(10), dbo.ReferralNotes.Note_date, 112) AS DateTime) AS Note_Date_Trunced

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.

I would say split the points between all of us.

We all burned a fair amount of time on this one.

Jim's recommendation of splitting the points makes sense to me.