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

  • Thread starter Thread starter mp80237
  • Start date Start 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?
 
Hello,

I have a Microsoft Access database linked ready only into another DB. All times in this DB are in numbers.
Click to expand...
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?
Click to expand...

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
 
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.
Click to expand...

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.
 
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.
Click to expand...

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
 
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.
 
Back
Top