Converted from LINUX time ok, but having issues with the between date.

M

mp80237

Hello,

I have a Microsoft Access database linked ready only into another DB. All times in this DB are in numbers. For example 1423496703 is actually 02/09/2015 3:45:03 PM. I was able to convert this using DateAdd('s',[open_date],"1970-01-01"). My issue is now doing a greater than or between date in criteria. I tried > '2014-12-31' and I got data type mismatch. So I tried
WHERE ((DateAdd('s',[open_date],"1970-01-01")>'2014-12-31'));
and got the same error. Any ideas?
Below is my SQL that works.

SELECT dbo_call_req.summary, dbo_call_req.description, DateAdd('s',[open_date],"1970-01-01") AS OpenDate
FROM dbo_call_req;

I tried
SELECT dbo_call_req.summary, dbo_call_req.description, DateAdd('s',[open_date],"1970-01-01") AS OpenDate
FROM dbo_call_req
Where DateAdd('s',[open_date],"1970-01-01") > "2014-12-31";

and I get the "Data type mismatch criteria expression."

Any ideas?
 
J

John W. Vinson

Hello,

I have a Microsoft Access database linked ready only into another DB. All times in this DB are in numbers.
For example 1423496703 is actually 02/09/2015 3:45:03 PM. I was able to
convert this using DateAdd('s',[open_date],"1970-01-01"). My issue is now
doing a greater than or between date in criteria. I tried > '2014-12-31' and
I got data type mismatch. So I tried
WHERE ((DateAdd('s',[open_date],"1970-01-01")>'2014-12-31'));
and got the same error. Any ideas?
Below is my SQL that works.

SELECT dbo_call_req.summary, dbo_call_req.description, DateAdd('s',[open_date],"1970-01-01") AS OpenDate
FROM dbo_call_req;

I tried
SELECT dbo_call_req.summary, dbo_call_req.description, DateAdd('s',[open_date],"1970-01-01") AS OpenDate
FROM dbo_call_req
Where DateAdd('s',[open_date],"1970-01-01") > "2014-12-31";

and I get the "Data type mismatch criteria expression."

Any ideas?

Date literals in Access must be delimited with octothorpes, not quotes, and
must be in either American mm/dd/yyyy format or (better) international
yyyy-mm-dd:

#1970-01-01#

will work in the above expressions.

Note that this newsgroup has been abandonded by Microsoft for several years
now and gets VERY little traffic or answers. I just happen to have it still on
my list. See my .sig for more current forums.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:

http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev
and see also http://www.utteraccess.com
 
N

Norman Peelman

Note that this newsgroup has been abandonded by Microsoft for several years
now and gets VERY little traffic or answers. I just happen to have it still on
my list. See my .sig for more current forums.

Note that it doesn't help when every reply you make directs people to
leave the group. The newsgroup doesn't need a corporate sponsor to operate.
 
J

John W. Vinson

Note that it doesn't help when every reply you make directs people to
leave the group. The newsgroup doesn't need a corporate sponsor to operate.

True. But it does need volunteers. There have been fewer than ten questions
answered here in the past SIX MONTHS.

If you like shouting into an empty barrel please feel free.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:

http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev
and see also http://www.utteraccess.com
 
M

mp80237

Thank you so much that worked. It was a hand slap to the head moment. I didn't realize this group was inactive. It has been a while since I posted anything.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top