time stamp issue at query level.

[GoodLife22]

Good morning everyone. I need some help PLEASE.

I have a front end Access 2000 DB and Backend SQL 2005.

My Form Prompts my user to enter company name and a start and finish date.

Then my query will run using information entered into that form. To spit out a dataset.

Query

SELECT C.CompName, C.TestDate
FROM tbl_RECORDS AS C
WHERE (((C.CompName)=[FORMS]![frm_STAT_DATA_COMP_DIALOG]![CompName]) AND ((C.TestDate)>=[FORMS]![frm_STAT_DATA_COMP_DIALOG]![Beginning Date] And (C.TestDate)<[FORMS]![frm_STAT_DATA_COMP_DIALOG]![End Date]));

The problem with this code is it leaves off the last date everytime due to the time stamp. So if I select my company then add the first date of 1/1/201 and last data of 5/5/2010 I will get everything from 1/1/2010 - 5/4/2010.

Now if I add +1 to the end of my criteria it will give me 1/1/2010 - 5/7/2010. It gives it more than one day from some reason.

Then if I try to change the format on my [TestDate] field at the query level it gives me no results at all.

What I am doing wrong here?

 

[boblarson]

When dealing with time elements you either have to use a date value for the field to compare against or you have to add the time element in or you have to add a day to the end (because when your criteria is just something like

12/31/2009

Then Access, against a date with a time element interprets it as

12/31/2009 12:00:00 AM

And so you miss out on the other 23:59 of the day.

So a suggestion is just to modify your query like this:

WHERE C.CompName)=[FORMS]![frm_STAT_DATA_COMP_DIALOG]![CompName] AND [B][COLOR=red]DateValue([/COLOR][/B]C.TestDate) [COLOR=red][B]BETWEEN[/B][/COLOR] [FORMS]![frm_STAT_DATA_COMP_DIALOG]![Beginning Date[B][COLOR=red]] And [[/COLOR][/B]FORMS]![frm_STAT_DATA_COMP_DIALOG]![End Date];

You could still use the >= <= but with just date values between is shorter.

Also, I removed the extra parens that Access adds (which really are not necessary) just to make it easier to read.

 

[GoodLife22]

Here is my code

SELECT C.CompName, C.TestDate
FROM tbl_RECORDS AS C
WHERE C.CompName=[FORMS]![frm_STAT_DATA_COMP_DIALOG]![CompName] AND DateValue(C.TestDate) BETWEEN [FORMS]![frm_STAT_DATA_COMP_DIALOG]![Beginning Date] And [FORMS]![frm_STAT_DATA_COMP_DIALOG]![End Date];

Error: The expression is typed incorrectly, or is too complex.... trying to simplify the expression by assigning parts of the expression to variables.

 

[boblarson]

This might help:

SELECT C.CompName, C.TestDate
FROM tbl_RECORDS AS C
WHERE C.CompName=[FORMS]![frm_STAT_DATA_COMP_DIALOG]![CompName] AND DateValue(C.TestDate) BETWEEN [B][COLOR=red]CDate([/COLOR][/B][FORMS]![frm_STAT_DATA_COMP_DIALOG]![Beginning Date][B][COLOR=red])[/COLOR][/B] And [B][COLOR=red]CDate([/COLOR][/B][FORMS]![frm_STAT_DATA_COMP_DIALOG]![End Date][B][COLOR=red])[/COLOR][/B];

 

[vbaInet]

After trying Bob's suggestion, you might want to wrap this correctly like this:

WHERE C.CompName=[FORMS]![frm_STAT_DATA_COMP_DIALOG]![CompName] AND [B][COLOR=Red]([/COLOR][/B]DateValue(C.TestDate) BETWEEN [FORMS]![frm_STAT_DATA_COMP_DIALOG]![Beginning Date] And [FORMS]![frm_STAT_DATA_COMP_DIALOG]![End Date][COLOR=Red][B])[/B][/COLOR];

Also, if TestDate is a true Date/Time field you don't need DateValue. If it isn't a Date/Time field, why isn't it anyway?

Edit: Just read Bob's first post and noticed it was his idea for the use of DateValue(). There is some validity to its use.

 

[boblarson]

Edit: Just read Bob's first post and noticed it was his idea for the use of DateValue(). There is some validity to its use.

The field is a date/time field which contains a time element. So, as I pointed out they can use various means to get what they want. I just like using DateValue because it strips off the time element and then you can just compare dates to dates.

However, I did suggest another method (two actually) which would be just as valid. One is to add the time elements to your date criteria (adding on + #12:00:00 AM# to the start value returned by the form reference and + #11:59:59 PM# for the end date criteria.)

Or by simply using the form references but using DateAdd to add a day on to the ending criteria so that you would get all records prior to midnight of that following day.

 

[boblarson]

Oh, and the parens are not required between the ANDS because ANDS don't need to be separated. If there were OR's then yes, you would need some to set the order of operations.

 

[vbaInet]

Ah, I didn't know it had a time element too. So, yep I concur with DateValue()

I'm just used to bracketing properly when it comes to logic. In Logic it is advised to bracket appropriately even when you know the compiler understands the comparison being set forth. There are some compilers where this rule is strict.

It wasn't the ANDs I was worried about, it was more how would the engine see AND Between AND. It might get a bit flustered

 

[boblarson]

It wasn't the ANDs I was worried about, it was more how would the engine see AND Between AND. It might get a bit flustered

I just dislike extraneous stuff and I've never really had a problem where the parens were left out unless there were both AND and OR involved. So that's why I removed them. It is okay to have them there but I tend to avoid extraneous stuff (or at least I've gotten that way as I've gotten along in age).

 

[vbaInet]

... (or at least I've gotten that way as I've gotten along in age).

That would be Brian's favourite line too Another year, another DateAdd("yyyy", 1, GetAge([DateOfBirth]))

 

[boblarson]

At my birthday in 2011 I become eligible for membership in the American Association of Retired Persons (AARP). Although it is looking like I'll have to work until death as I have nothing to retire on.

 

[vbaInet]

You will probably need some professional advice to get you on a fast track retirement plan. There must be options out there that would suit you so you could speak to a professional and get some advice from pensioners. But you need to act quick. You can't do it on your own.

 

[boblarson]

You will probably need some professional advice to get you on a fast track retirement plan. There must be options out there that would suit you so you could speak to a professional and get some advice from pensioners. But you need to act quick. You can't do it on your own.

I don't have any extra money to put aside. It all is getting eaten up by normal bills (rent, car, insurance, utilities and medical bills - lots of medical bills).

 

[vbaInet]

It might be time to consider cutting down.

(rent,...

Maybe move into a smaller apartment. Or consider moving to a different suburb where accom is cheaper, quality of life is good and job prospects are ok (with your experience and skills taken into consideration).

(... car, ...

It might be worth considering getting rid of if it's not an absolute necessity

(...insurance, utilities...

Shop around for cheaper and always call them up for deals.

There might be more than can be looked at but that's what a professional does. They go through your whole expenditure and advice on areas to cut down or spend less on (or even offer alternatives) in order to generate savings.

 

[GoodLife22]

I have still been having problems with this. I think I figured out why. It looks like some of the records have the current formatting of

"mm/dd/yyyy" while other records have "mm/dd/yyyy hh:mm:ss: PM"

So when I write these small scripts it is not working correctly due to the different date variations. I think some forms allow the time stamp to be added while other forms only allow the short date layout. I will try to correct all forms to match. With that said I need to correct ALL of the records with no time stamp in the DB.

Is there a way to say add a time stamp of 8:05 AM to all records with no time stamp currently?

Then I may be able to get the "DateValue" to work correctly.

Thank you.

 

[GoodLife22]

This was originally a Access table that was later converted to SQL. In the SQL table the field type is "datetime" if it matters.

 

[GoodLife22]

Nevermind. It was a NULL issue. Once I removed all null records from my query the errors stopped. I think I should be good. Thank you for your help. As always I will gladly donate to your cause for all of the help Bob.