Need help using DateAdd function with DB2 table link 1

[pheffley]

I'm trying get some queries to work that use the DateAdd function. The queries originally refernced another Access DB, but now the tables I'm referencing reside in DB2 and are accessed by a link (actually they are views). I can query dates without error if no functions are used. However, I think DB2 stores and returns dates as yyyy/mm/dd and I'm not sure what Access is expecting. The criteria for the query is: <DateAdd(&quot;yyyy&quot;,-1,Date()). I get the following error:

SQL0181N - The string representation of a datetime value is out of range

Anyone have an idea on how to get this to work?

Thanks,

Paul

 

[scriverb]

The date is probably being returned as a string including the slashes. To convert this and make it useful in access you can use the DateSerial function:
DateSerial(Left(DateField, 4), Mid$(DateField, 6, 2), Right(DateField, 2))

This function now makes it understandable to ACCESS. Now you can use this to perform a DateAdd function:
DateAdd(&quot;yyyy&quot;,-1,DateSerial(Left(DateField, 4), Mid$(DateField, 6, 2), Right(DateField, 2)))

Give this a try and see if it works. If not we have to figure out if it is truly coming in as a string.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

Now I'm getting a type mismatch error

 

[scriverb]

Okay, let's first try the DateSerial function to see if that is where the type mismatch is. Try running some code to analyze and see if the DateSerial function provided is in fact extrapolating the string value from the DB2 connection to an ACCESS date field.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

I just ran the DateSerial function only:

DateSerial(Left(Date(),4),Mid$(Date(),6,2),Right(Date(),2))

And I still get the type mismatch. Is this the type of test I needed to do, or were you talking about something else?

Thanks,

Paul

 

[scriverb]

I see the problem. You have substituted the Date() function(todaysdate) for what I posted for you. The reference to DateField in my posting was referring to the date field being returned in your records from the DB2 database table.

DateSerial(Left(DateField, 4), Mid$(DateField, 6, 2), Right(DateField, 2))

Use this in a query and change the red references above to the field with the datevalue coming in from the DB2 table.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

Still got the type mismatch. Here is the full query:

SELECT DISTINCTROW FDATA.BIRTHDATE
FROM FDATA
WHERE (((FDATA.BIRTHDATE)=DateSerial(Left(FDATA.BIRTHDATE,4),Mid$(FDATA.BIRTHDATE,6,2),Right(FDATA.BIRTHDATE,2))));


Sorry for this...It's been a long time since I've programmed in Access (plus I've never encountered this before!)

 

[scriverb]

Let's just run this and get the column working. After that the DateAdd will work.

SELECT DISTINCTROW FDATA.BIRTHDATE, DateSerial(Left(FDATA.BIRTHDATE,4),Mid$(FDATA.BIRTHDATE,6,2),Right(FDATA.BIRTHDATE,2)) as DateSerialDate
FROM FDATA;

What we are looking for here is exactly how access is treating this field BIRTHDATE. Is it converting it to a date field already or is it returning a string representation of a date field.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

The birthdate returns okay, but the new column, DateSerialDate, returns &quot;#Error&quot;.

 

[scriverb]

The error means that it is not a string value.

Try this:
SELECT DISTINCTROW A.BIRTHDATE, DateValue(Format(A.BIRTHDATE, &quot;MM/DD/YYYY&quot) as DateValueDate
FROM FDATA as A;

If we get a valid date in the second column then we can perform the DateAdd on that expression.




Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

That worked...both columns returned the correct values.

 

[scriverb]

It seems now you can use the following as your WHERE clause in your query.
WHERE DateValue(Format(A.BIRTHDATE, &quot;MM/DD/YYYY&quot) < DateAdd(&quot;yyyy&quot;,-1,Date())

You can also use the following to display the column as you want it:
DateValue(Format(A.BIRTHDATE, &quot;MM/DD/YYYY&quot) as DOB

I think that this should be everything you need to set up your query. Make sure you substitute the appropriate table name for the A alias designation.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

Bob, I and the Oklahoma Department of Human Services thanks you!

 

[scriverb]

pheffley, my pleasure. Just retired from the Michigan Dept. of Social Services(Family Independence Center). Seems like home.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???