Need help with complex query

[BHA TECH]

I've been banging my head on the desk today. The Boss wants a report based on a payments table which includes, 12 date fields.
The query should return data for any record which has a match in any of these fields.

<code>SELECT *
FROM DETAILS
WHERE (((DETAILS.PaymentDate) Like Date())) OR (((DETAILS.PaymentDate1) Like Date())) OR (((DETAILS.PaymentDate2) Like Date())) OR (((DETAILS.PaymentDate3) Like Date())) OR (((DETAILS.PaymentDate4) Like Date())) OR (((DETAILS.PaymentDate5) Like Date())) OR (((DETAILS.PaymentDate6) Like Date())) OR (((DETAILS.PaymentDate7) Like Date())) OR (((DETAILS.PaymentDate8) Like Date())) OR (((DETAILS.PaymentDate9) Like Date())) OR (((DETAILS.PaymentDate10) Like Date())) OR (((DETAILS.PaymentDate11) Like Date()));</code>

I'm not exactly sure where I went wrong. The query returns only one result instead of all results. Thus far, there are only two entries in the DB. One which has a value of 01/01/10 for PaymentDate and one record which includes 01/01/10 for PaymentDate3. Both should appear when this query is run, but for the all the RedBull in Manhatten, I can't get this to work. It has to be something terribly simple.
The columns are all the same, Date with a Short Date Format. I've tried many variations of the above with no success. The query seems to pull data from the first column but proceeds no further.

Oh great gurus of MS ACCESS, hear my plea and lend aid to this troubled tech.

 

[Brianwarnock]

I presume that if there is no date then the field is Null so change your Like(date()) for Is Not Null. Cant remember if dates can be empty strings so you may have to test for "" as well.

Brian

 

[BHA TECH]

I wasn't clear enough I think. Although your method would pull results, it would not pull results based on today's date.
The idea here is to find all records with a field that matches today's date. There are 12 fields, each containing a specific date or as you guessed, a null value. But if the dates do not match, then a query for not null values is useless.
Since Date() pulls todays date, I'm hoping that I can create this query to pull all records with any of the 12 date fields match Date().

It doesn't seem so hard. It is possible to search all columns of a table for whatever value you want. Just not sure why this one has me stumped.

 

[MSAccessRookie]

I've been banging my head on the desk today. The Boss wants a report based on a payments table which includes, 12 date fields.
The query should return data for any record which has a match in any of these fields.

<code>SELECT *
FROM DETAILS
WHERE (((DETAILS.PaymentDate) Like Date())) OR (((DETAILS.PaymentDate1) Like Date())) OR (((DETAILS.PaymentDate2) Like Date())) OR (((DETAILS.PaymentDate3) Like Date())) OR (((DETAILS.PaymentDate4) Like Date())) OR (((DETAILS.PaymentDate5) Like Date())) OR (((DETAILS.PaymentDate6) Like Date())) OR (((DETAILS.PaymentDate7) Like Date())) OR (((DETAILS.PaymentDate8) Like Date())) OR (((DETAILS.PaymentDate9) Like Date())) OR (((DETAILS.PaymentDate10) Like Date())) OR (((DETAILS.PaymentDate11) Like Date()));</code>

I'm not exactly sure where I went wrong. The query returns only one result instead of all results. Thus far, there are only two entries in the DB. One which has a value of 01/01/10 for PaymentDate and one record which includes 01/01/10 for PaymentDate3. Both should appear when this query is run, but for the all the RedBull in Manhatten, I can't get this to work. It has to be something terribly simple.
The columns are all the same, Date with a Short Date Format. I've tried many variations of the above with no success. The query seems to pull data from the first column but proceeds no further.

Oh great gurus of MS ACCESS, hear my plea and lend aid to this troubled tech.

I always get troubled by the extra punctuation that MS Access provides, so I have rewritten the WHERE clause, in order to make it a little easier to read, although the result may be the same.

[FONT=Courier New][B]SELECT *[/B][/FONT]
[FONT=Courier New][B]FROM DETAILS[/B][/FONT]
[FONT=Courier New][B]WHERE [/B][/FONT]
[FONT=Courier New][B]    ((DETAILS.PaymentDate   Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate1  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate2  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate3  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate4  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate5  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate6  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate7  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate8  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate9  Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate10 Like Date()) OR[/B][/FONT]
[FONT=Courier New][B]     (DETAILS.PaymentDate11 Like Date()))[/B][/FONT]

 

[BHA TECH]

I tried your revised code, although it is much cleaner, it produces the same results. It pulls the record with a matching date in the first field, but no files with matching dates in any of the other 11 fields.

 

[Brianwarnock]

Sorry thought that you were using Like Date() to mean like a date as normally one would just put =

Brian

 

[MSAccessRookie]

I tried your revised code, although it is much cleaner, it produces the same results. It pulls the record with a matching date in the first field, but no files with matching dates in any of the other 11 fields.

Can you show the Structure of your table? Column names and types should be enough and no data will be required.

 

[BHA TECH]

This is why I absolutely love PHP/MySQL as opposed to Access/MSSQL.
Unfortunately that is not an option here.
Brian, it's ok man.

Anyone else have any ideas or thoughts? At this point I'm ready to change the entire table to TEXT fields, just to make this a hundred times easier. Who would have thought date fields were so limited as to be almost completely useless.

The query seems syntactically correct, from everything I've read and understand, this should work as fluidly as any other query.

MS ACCESS, We've been seeing each other for some time now, but I just don't think this relationship is headed to a place where I can follow. It's not you, it's me. I need flexibility, reliability, speed and most importantly, true support. We can still hang out, I just think it's time we saw other languages and platforms thats all. Awww, come on MS ACCESS, don't cry. You did your best and thats something to be proud of. You'll always be my first and we'll always have that time in Vegas.

 

[BHA TECH]

The table structure is simple,
ID, First_Name, Last_Name, PaymentDate, PaymentAmount, PaymentDate1, PaymentAmount1 etc... through PaymentDate11, PaymentAmount11, Status

ID is autonumber
First & LAst Name are both TEXT
PaymentDates are all date fields with short date formatting.
PaymentAmounts are all Currency with 2 decimal places.

 

[Brianwarnock]

Just to satisfy my curiosity I tried it on a simple test db with a couple of date fields and it worked fine using Like, so I'm left with only one thought which is unlikely from what you have said and that is that the other field has some how got a time element in it that is not 00:00:00.

To help more we probably need to get hold of the DB, as for switching to Text dates that is a real no go.

Brian

 

[BHA TECH]

The time element was one of my first thoughts and after reviewing the data itself I can say that there is no field which includes the time, they are formatted for shortdate, which seems to insert just the date but not time.

As for text dates, it's works fine, just no date functions per se. The data is entered exactly the same. There are no additional functions like getting the number of days from a date or anything like that. Simply a stored value. Which from my experience works better in TEXT than in DATE as far as MS ACCESS is concerned. I've never had the problems I have with DATE fields when using text fields to do the same job.
Maybe your right, but every time we use Date fields, we run into these issues. Switching to text with a few workarounds has been standard practice here for over 4 years.

 

[BHA TECH]

You are right though. I created a quick sample DB using the same fields and the query worked fine. It pulled three records, each having todays date but in differing columns. Well, at least I know the query works...now to track down this new issue.

 

[BHA TECH]

would it make a difference if the test DB uses Access and the real DB uses MSSQL?

 

[BHA TECH]

fixed. use DateValue() instead of Date() to get just the date and not date/time.

 

[namliam]

I presume that if there is no date then the field is Null so change your Like(date()) for Is Not Null. Cant remember if dates can be empty strings so you may have to test for "" as well.

Brian

A date is not a string, a date in actuallity is a double.... so using LIKE or and empty string ("") is just wrong...

The short date format of the control hides any TIME part of the date field, any date field in (most) database(s) is a date/time field that contains (visible or hidden) both date and time.

The actual move from Access to SQL Server:
Now() > GetDate()
Date() > No direct equivalent
Needs to be calculated via: cast( floor ( cast ( getdate() as float ) ) as datetime )
Or something simular

For dates to work with time you use something like
Yourdate >= Date() and Yourdate < Date + 1
Or the SQL Server equivalent:

    Yourdate >= cast( floor ( cast ( getdate() as float ) ) as datetime )
and Yourdate <  cast( floor ( cast ( getdate() as float ) ) as datetime ) + 1

Problem with between is it includes the "limits" thus would including midnight of the next day, which you do not want.
Alternatively you can use the last second of the day and use between:
Yourdate Between Date() and Yourdate < Date + #23:59:59#
Or the SQL Server equivalent:

Yourdate between cast( floor ( cast ( getdate() as float ) ) as datetime ) 
             and cast( floor ( cast ( getdate() as float ) ) as datetime ) + cast ('23:59:59' as datetime)

I hope this helps

 

[Brianwarnock]

fixed. use DateValue() instead of Date() to get just the date and not date/time.

I just don't understand this. You assured us that there were no times.
I know what Datevalue(expression) does but have never seen DateValue(), how did you use it?

Brian

 

[BHA TECH]

That makes sense. The fields didn't show a time, even though it was there due to formatting. I thought I had it yesterday, which worked on testDB but not on SQL. I will try your code today and hopefully, we'll have a working prototype by noonish. Any other suggestions are greatly appreciated. Thanks for all the help. I've been looking at alot of the work done before I got here and it's filled with TEXT fields that hold date values because they could not figure it out apparently. But I really want to take this to the next level, which means getting these dates to work properly and consistently.

 

[BHA TECH]

DateValue() did not work. I was wrong. I got excited and posted too quickly.
As for times, I had thought the times were not being introduced into the system. I still don't think they are, but it seems maybe it simply wasn't showing me the times saved on the SQL server.

The original query works well in ACCESS but horrendous VS SQL server. I'm going to go get a cup of coffee and spend another day working on this. The boss is telling me to change them to TEXT like his last tech and get it up and running.

It would seems to me that working with dates in ACCESS is almost impossibly difficult. One would think comparing dates would be a basic essential function.

 

[BHA TECH]

Yourdate between cast( floor ( cast ( getdate() as float ) ) as datetime )
and cast( floor ( cast ( getdate() as float ) ) as datetime ) + cast ('23:59:59' as datetime)

tried...
PaymentDate between cast( floor ( cast ( getdate() as float ) ) as datetime ) and cast( floor ( cast ( getdate() as float ) ) as datetime ) + cast ('23:59:59' as datetime)
tried...
between cast( floor ( cast ( getdate() as float ) ) as datetime ) and cast( floor ( cast ( getdate() as float ) ) as datetime ) + cast ('23:59:59' as datetime)


Does not work. invalid syntax. Good try though, it looked impressive. Just wish it worked.

 

[namliam]

The boss is telling me to change them to TEXT like his last tech and get it up and running.

Tell him he is full of it and should shut up... text for a date.... *BRRRR*

It would seems to me that working with dates in ACCESS is almost impossibly difficult. One would think comparing dates would be a basic essential function.

Its not impossibly difficult, it is not more difficult than working with dates in any other database.... It is the lack of comprehension of how dates work and what you have to do to make them work for you that is making it difficult.

Does not work. invalid syntax. Good try though, it looked impressive. Just wish it worked.

I can guarantee you it works ! Assuming you are doing query on SQL SERVER.

If you are working in access,
Yourdate Between Date() and Yourdate < Date + #23:59:59#
or
Yourdate >= Date() and Yourdate < Date + 1
Will work for you...