Access Criteria Problem (1 Viewer)

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
I have an access database which is basically a downtime log holds information like date / start / end / duration / department / area /

Okay, so I have a problem with the date part. In my query, I used an expression to abstract the date from the field start using format([start]-0.25,"DD/MM/YYYY")

The -0.25 is an indicator that checks if the time is between 00:00:00 and 06:00:00 if so gives the previous day's date.

From this field, I base a report which uses form filters to filter out by the day however if I use a date range it only filters by the "DD" and not the "DD/MM/YYYY".

For instance if I put 01/09/2015 and 20/09/2015 every date that is between 01 and 20 will show.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:09
Joined
May 7, 2009
Messages
19,169
should you also not include the Date field in your query expression:

Format(CDate([Date] & " " & [Start])-0.25, "dd/mm/yyyy")
 

spikepl

Eledittingent Beliped
Local time
Tomorrow, 00:09
Joined
Nov 3, 2010
Messages
6,144
format([start]-0.25,"DD/MM/YYYY")

is a string and not a date, so it will look for the first letters(digits it can match and that's it

you need to use #, i.e. write #DateAsString#

Second, SQL requires dates given as mm/dd/yyyy
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
format([start]-0.25,"DD/MM/YYYY")

is a string and not a date, so it will look for the first letters(digits it can match and that's it

you need to use #, i.e. write #DateAsString#

Second, SQL requires dates given as mm/dd/yyyy

Hi Spikepl,

Can you explain further or show me an example of how the expression should be built?
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
Okay so i went away and tried various things with no joy so far i got

DateShift: Left(CDate([Incident_Start]-0.25),InStr(CDate([Incident_Start]-0.25)," "))

Am i going about it the right way?

When displayed in the datasheet it looks fine but still getting the problem with the form filter?
 

Minty

AWF VIP
Local time
Today, 23:09
Joined
Jul 26, 2013
Messages
10,355
If your incident_start is a datetime field you can set the criteria and leave it as a datetime by using the dateadd function

dateadd("h",-6,[incident_start]) this will subtract 6 hours from your date time

DateShift: Int( dateadd("h",-6,[incident_start]) ) Will give you your date only
 

spikepl

Eledittingent Beliped
Local time
Tomorrow, 00:09
Joined
Nov 3, 2010
Messages
6,144
Start over.

n my query, I used an expression to abstract the date from the field start using format([start]-0.25,"DD/MM/YYYY")

Where? If [start] is a datetime field in the query then [start]-0.25 will give you datetimes 6 hours earlier. if start is a datetime then forget about all that format stuff

Update:

Show the SQL of the query and say what datatype each field is. Otherwise this will take ages to sort out
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
Start over.



Where? If [start] is a datetime field in the query then [start]-0.25 will give you datetimes 6 hours earlier. if start is a datetime then forget about all that format stuff

Update:

Show the SQL of the query and say what datatype each field is. Otherwise this will take ages to sort out

Okay,

Code:
SELECT Format([Incident_Start]-0.25,"dd/mm/yyyy") AS DateShift, Downtime.Incident_Start
FROM Downtime
WHERE (((Format([Incident_Start]-0.25,"dd/mm/yyyy"))=[Forms]![Downtime_Query]![DateShift])) OR (((Format([Incident_Start]-0.25,"dd/mm/yyyy")) Between [Forms]![Downtime_Query]![firstdate] And [Forms]![Downtime_Query]![seconddate]));

Text Boxes are unbound with format as Short Date
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
Hi,

I just tried to supply you a copy of the database however I cant due to current post restrictions.
 

obeylele

Registered User.
Local time
Tomorrow, 03:09
Joined
Jul 29, 2005
Messages
28
Have you tried this? I think you should try this

If your incident_start is a datetime field you can set the criteria and leave it as a datetime by using the dateadd function

dateadd("h",-6,[incident_start]) this will subtract 6 hours from your date time

DateShift: Int( dateadd("h",-6,[incident_start]) ) Will give you your date only
 

spikepl

Eledittingent Beliped
Local time
Tomorrow, 00:09
Joined
Nov 3, 2010
Messages
6,144
I asked what type the fields were, you did not answer that! And your query is a mess, you should pay attention to data types and not switch between data types! Look up the Format function in the documentation, before you create any more accidents by applying it :D

I presume the fields in the query all are of type datetime, so this

Code:
SELECT Format([Incident_Start]-0.25,"dd/mm/yyyy") AS DateShift, Downtime.Incident_Start
FROM Downtime
WHERE (((Format([Incident_Start]-0.25,"dd/mm/yyyy"))=[Forms]![Downtime_Query]![DateShift])) OR (((Format([Incident_Start]-0.25,"dd/mm/yyyy")) Between [Forms]![Downtime_Query]![firstdate] And [Forms]![Downtime_Query]![seconddate]));



should have been this:

Code:
SELECT Incident_Start-0.25 AS DateShift, Downtime.Incident_Start
FROM Downtime
WHERE [Incident_Start]-0.25=[Forms]![Downtime_Query]![DateShift] OR [Incident_Start]-0.25 Between [Forms]![Downtime_Query]![firstdate] And [Forms]![Downtime_Query]![seconddate]));



Note that in the above you demand a very precise [Incident_Start]-0.25=[Forms]![Downtime_Query]![DateShift] . I have no idea what this is supposed to be, but this is highly unlikely anything desirable. Make what you want initially one and only one criterion at the time, or you'll never sort this out

Correction : this format BS also cuts off the time component if any, so:

Code:
SELECT Datevalue(Incident_Start-0.25) AS DateShift, Downtime.Incident_Start
FROM Downtime
WHERE datevalue([Incident_Start]-0.25)=[Forms]![Downtime_Query]![DateShift] OR [Incident_Start] Between [Forms]![Downtime_Query]![firstdate] +0.25 And [Forms]![Downtime_Query]![seconddate]+0.25));
 
Last edited:

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
Hi Obeylele,

I have tried but still can't get the search filter to work it shows up a blank record.
 

Minty

AWF VIP
Local time
Today, 23:09
Joined
Jul 26, 2013
Messages
10,355
Hi Obeylele,

I have tried but still can't get the search filter to work it shows up a blank record.

As Spike has pointed out - and I don't think you have picked up on it - date time fields always contain a date element and a time element. They are stored as a decimal number where the integer part is the date and the value to the right of the decimal point equals the time.

When you are setting your criteria to [Field] = 21/10/2015 where field is a datetime value what you are actually saying as far as Access is concerned is
[Field] = 21/10/2015 00:00:00

Assuming you haven't stripped the time from your [Field] you are therefore probably checking for
[21/10/2015 13:12.46] = 21/10/2015 which as you have seen will hardly ever return any results.

You need to check for dates BETWEEN values to check for this issue.
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
The actual SQL was autogenerated from me creating the said query which is why its all over the place.

I just saw that on the exported excel sheet the DateShift field actually looks like 21/09/2015 00:00 all the way down changing the date where it should.

On my search form, how do I get the textbox to search like a date/time. I'm really confused on how to go about it all and I do really appreciate all your help your giving me
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
Okay is there any other way around doing this cause i am confused even more now I haven't changed anything at all exported the same document and the dates are formatted 01-Sept-15
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
Code:
SELECT Datevalue(Incident_Start-0.25) AS DateShift, Downtime.Incident_Start
FROM Downtime
WHERE datevalue([Incident_Start]-0.25)=[Forms]![Downtime_Query]![DateShift] OR [Incident_Start] Between [Forms]![Downtime_Query]![firstdate] +0.25 And [Forms]![Downtime_Query]![seconddate]+0.25));

I get a parse error with the above code without the )); i get an error

This expression is typed incorrectly or it is too complex

Code:
SELECT Datevalue(Incident_Start-0.25) AS DateShift, Downtime.Incident_Start
FROM Downtime
WHERE datevalue([Incident_Start]-0.25)=[Forms]![Downtime_Query]![DateShift] OR [Incident_Start] Between [Forms]![Downtime_Query]![firstdate] +0.25 And [Forms]![Downtime_Query]![seconddate]+0.25));
 

Redous

Registered User.
Local time
Today, 23:09
Joined
Feb 22, 2015
Messages
13
ooooooh i think i have done it i will get back to you :)
 

obeylele

Registered User.
Local time
Tomorrow, 03:09
Joined
Jul 29, 2005
Messages
28
So you tried both solutions at once as shown in your query.(attached pic)
I think you should remove link to your db if you problem is solved..
 

Attachments

  • error.jpg
    error.jpg
    59 KB · Views: 102

Users who are viewing this thread

Top Bottom