Problems with Date criteria in a query

[ScottyJones]

Hi all,
I have a query based on a query based on a table.
The table has 2 fields of date/time... "PStart_Time" and a "PEnd_Time".
In the first query I am splitting the Start and End Times into 2 fields one being the same field as before (untouched) and the other like this:
StartDate: Format([PStart_Time], "Short Date")
EndDate: Format([PEnd_Time], "Short Date")

So far so good.

Either in this 'first query' or in the 'second one' I am adding a >[Date:] Criteria to the "StartDate" field. When I enter the Date I want it displays all dates in the table. It does not matter what date I give it, the result is the same, all dates in the table.
I have also tried Between [Date:] And [Date:] I get 0 results.

If I run >[Date:] in the Criteria field of the original PStart_Time field it works with no issues, but I want to have the user just enter 1 date and get the results they need.

I would think that since I am converting this data to "Short Date" format that I should be able to do =[Date] in either of these new fields and it should show me all records where Start Date = the date I put in the msgbox window, but it doesn't, I get 0 results. I do not understand the point of converting the field to "Short Date" format if I can't use said date to run a query against the data.

Could someone please explain 1) who can I get this to ask a user to enter 1 single date in history and only get results for that date? and 2) what the point of converting a 'general' or 'long' date to 'short' if I can't use it for anything.


Thanks you in advance!!

 

[Galaxiom]

Names should not include special characters like a colon. Nor should they use reserved words like Date.

Format() return a string that depends on your Regional Date format. The comparison will probably compare them as strings rather than dates.

 

[GinaWhipp]

Hmm, you might try...

CDate(Format([PStart_Time], "Short Date"))

...as that Format() function is turning your Date into a string.

 

[plog]

Could someone please explain 1) who can I get this to ask a user to enter 1 single date in history and only get results for that date?

Do not put the criteria under the Format field, bring in the actual date field into your query and apply the criteria there. Like Galaxiom said, Format() converts whatever you are working with to a string. Strings get compared character by character. That means:

11/11/2018 comes before 7/7/2018 (because 1 is before 7)

2) what the point of converting a 'general' or 'long' date to 'short' if I can't use it for anything.

Think of Format in the general sense of the word--like in MS Word. When you format something you make it pretty/add style to it, that's it. Same thing in Access, when you Format () something you make it have a specific style, in doing so it converts that to a string. When humans write dates they can have many formats (mm-dd-yyyy: Month Name, d yyyy: m/d/y, etc). Format allows you to achieve all those different formats and display them as you wish. The cost though, is that whatever you were working with is now a string.

 

[ScottyJones]

Thanks Plog for the Explanations.
I appreciate it.
I guess I have to live with it this way.

I just find it difficult to believe that I can't extract a [date] out of a [date.time] and use it as criteria for a query with out having to do 'between' and prompt for 2 dates every time.

Anyway Thanks again all for your help!!

 

[bob fitz]

..........I just find it difficult to believe that I can't extract a [date] out of a [date.time] and use it as criteria for a query with out having to do 'between' and prompt for 2 dates every time......

Plog said:

Do not put the criteria under the Format field, bring in the actual date field into your query and apply the criteria there.

Have you tried this.

 

[plog]

I can't extract a [date] out of a [date.time]

You moved the goalpost with that comment. My advice still holds, but in response to this statement, it is possible with DateValue():

https://www.techonthenet.com/access/functions/date/datevalue.php

 

[ScottyJones]

Plog,
Not sure I understand your goalpost comment. So I apologize if I am doing something wrong or rude or if I am misunderstanding you.
I mean no disrespect.

I have already tried DateValue() on 2 different fields and I have 2 different issues with it.
1. On the field I really need this to work with "PStart_Time" in which the Data looks like "8/22/18 15:45:41" but is formatted as "Short Time" in the original table and I put the expression "StartDate: DateValue([PStart_time])" in the next field and use any criteria format of "Date()" or "[Which Date:]" I am getting Data Type Mismatch error. I've been through the data and cannot find anything wrong with the dates or times. So am I doing something wrong in how, when, where I am calling the data out?

However, On a different field that is just a date to begin with:
(Understand, I don't need it to work here on this field, this was just a test).

"Order_Date" followed by the expression "ODate: DateValue([Order_Date])"
I get 0 results when my Criteria on the ODate field is "[Which Date:]" and I type 8/22/18 into the prompt window. But if my criteria on ODate is "Date()-3" then it works just fine. If I put "[Which Date:]" in the "Order_Date" field and type 8/22/18 into the prompt window it works just fine.

So even if I can figure out the Data Type Mis-Match on the important field [PStart_Time] that I need to work, I will still need it to prompt the user for the date they want to look at, and it doesn't seem to work with ODate anyway. Is there something wrong with the format "8/22/18" that it doesn't work with DateValue() in a prompt?

It just doesn't seem to work no matter what I do.

The only thing I can think of at this point is to go back to the original table and add a date field and have the input form's check box "After Update" event add the time to one field and the date to the other field so I can use it the way I want - it just seems silly to have to hold the date and time in 2 different fields when I already have it all in one field.


Again I am sorry if I am being rude - I do greatly appreciate all your help!!


Scott

 

[MajP]

SELECT tblDateTime.DateTimeField
FROM tblDateTime
WHERE Int([DateTimeField])>CDate([Input Date mm/dd/yyyy])

Works for me on a field formatted as short date and a input parameter entered as mm/dd/yyyy

 

[MajP]

Demo to explain why

Public Sub DateTimeDemo()
  Dim DateTime As Date
  Dim strDateTime As String
  DateTime = #8/25/2018 12:54:00 PM#
  'This date is stored in access as the number 43337.5375
  'The integer is the date, the decimal is the time portions
  strDateTime = "8/25/2018"
  '
  Debug.Print "Remove Time Portion " & Int(DateTime)
  Debug.Print "Display the date time as a number: " & CDbl(DateTime)
  Debug.Print "Display the date portion as a number: " & CDbl(Int(DateTime))
  Debug.Print "Convert as string to a date " & CDate(strDateTime)
  Debug.Print "Display the string converted date as a number: " & CDbl(CDate(strDateTime))
  Debug.Print "Show they are equal: " & (Int(DateTime) = CDate(strDateTime))
  End Sub

Results

Remove Time Portion 8/25/2018
Display the date time as a number: 43337.5375
Display the date portion as a number: 43337
Convert as string to a date 8/25/2018
Display the string converted date as a number: 43337
Show they are equal: True

 

[MajP]

After rereading there might be some confusion.
If this date Time, 8/25/2018 12:54:00 PM, is in a field and that field has a format applied to it, no matter what format you see it still has the value
43337.5375
That is different than using the format function in a calculated field. There is really no reason ever to convert to a string format and then do a comparison. Convert the values to dates or numeric to do the comparison.

 

[ScottyJones]

Hi All!!
Sorry I went Silent for a while - I needed a break or I was going to delete the entire database, quit my job and go live in the woods somewhere...
yeah it got that bad....

So after a time of cooling down I finally figured out my issue.
You ever play "Where's Waldo" and there is someone right next to you that already knows where he is on the page and you just can't see him...?
Yeah that's kind of how this is. It was in your explanations but for some reason I couldn't see it. So I'm posting this just in case it helps someone from going crazy like I did in the future.

The Main issue came down to entering the desired date in the prompt window.

If I use the criteria field to ask for a specific date [Which Date do you want?] I have to enter the date in the prompt window as 9/11/2018 or 9/9/2018 (M/D/YYYY).
As opposed to 09/09/18 or 9/11/18 or 09/09/2018 will not work, I get zero results.
The only way I get results is by using M/D/YYYY (no leading zeros and a 4 digit year) but it works every time.

The only thing I don't get is if I use the criteria ">[Which Date?]" I can enter 9/9/18 (2 digit year) and it will work and show me everything greater than September 9th, 2018... So why Greater than (or Less than) works with a 2 digit year and prompting for a specific date does not I don't know but I just ran the test several times with different criteria prompts and it seems to work as I have detailed above.

I figured it out when I put #9/11/18# in the Criteria field in the query, and Access changed it to #9/11/2018# So that's when I put it in the prompt window with the 4 digit year and BINGO it worked.

Thanks again for all your attempts at getting it through my head.
Honestly - I'm not always this dense...