mismatched datatype in access query

Question

So i have a query that is suppose to get me data from two tables. Which works fine so long as the date format is under the english(us) locale. When it is in an international formating the query gets an error when run and fails. the query is

        SELECT DISTINCT AlarmDet.Machine, AlarmDet.Job, 
AlarmDet.Mode, AlarmDet.User, AlarmDet.JobStart,
 AlarmDet.Object, AlarmDet.AlarmId, AlarmDet.AlarmStart, 
AlarmDet.MachineFault, AlarmDet.OperFault,
     AlarmDet.PiecesFed, Val(OperFault)+Val(MachineFault) AS AlarmStopTime, 
    (Mid(alarmdet.AlarmStart,5,7) & Right(alarmdet.AlarmStart,4) & Mid(alarmdet.AlarmStart,11,9)) AS AlarmTimeDate, 
    Mid(alarmdet.AlarmStart,12,2) AS AlarmHH, 
    Mid(alarmdet.AlarmStart,15,2) AS AlarmMM,
 Mid(alarmdet.AlarmStart,18,2) AS AlarmSS, 
CVDate((Mid([alarmdet].[AlarmStart],5,7) & Right([alarmdet].[AlarmStart],4))) AS AlarmDate, Alarms.ALARM_DESC
    FROM AlarmDet INNER JOIN Alarms ON AlarmDet.AlarmId = Alarms.id;

What the formulas in the query are doing is taking alarmstart (date) and splitting it up into seperate segments.

The section of the query that is causing an error when i step into it and attempt to run it is this:

CVDate((Mid([alarmdet].[AlarmStart],5,7) & Right([alarmdet].[AlarmStart],4))) AS AlarmDate

What i would like to know is, if there is a way in access to convert the formating of the query to be turnned in to US dateformating? Or is there a better way to structure this part of the query in order to get it to run under an international formating?

Update

after spending some time and trying some of your suggestions, i've come up with this:

format(Mid([alarmdet].[AlarmStart],5,7) & right([alarmdet]![alarmstart], 4), "mm/dd/yyyy")

However, instead of getting mm/dd/yyyy, i get this: mm-dd-yyyy.

Any thoughts as to why that is?

Update: again...

Nevermind, it appears that, that has to do with where your locale is set to.

Any suggestions or help is greatly appreciated.

Thanks.

All you need do is pass your date criterion in a string format that is unambiguous, e.g., mmm-dd-yyyy. It doesn't have to be a US date format, just one that can't be interpreted in multiple ways (which means months have to be spelled out, not as digits). — David-W-Fenton, Aug 26 '11 at 21:26

score 1 · Answer 1 · answered Aug 26 '11 at 19:32

1

Try using ACCESS's Year() and Month() functions on the date field, rather than the MID and Right function to parse the date... I don't have ACCESS installed, but it would be something like:

CVDate(Str(month([alarmdet].[AlarmStart]))&
     "/"&str(year([alarmdet].[AlarmStart]))) AS AlarmDate

You might have to tweak it a bit, but those functions should extract date information properly...

answered Aug 26 '11 at 19:32

Sparky

14,967
2
31
45

Isn't DateSerial() the usual solution to this problem? – David-W-Fenton Aug 26 '11 at 21:24

HansUp · Accepted Answer · 2011-08-31T14:43:41.037

1

See whether the Format() function will allow you to display [AlarmStart] as you wish. Here are some sample expressions copied from the Immediate Window.

? Format(Date(), "yyyy/mm/dd")
2011/08/29

? Format(Date(), "mmm d, yyyy h:nn:ss ampm")
Aug 29, 2011 12:00:00 AM

Edit: This is what I get in the Immediate Window using the example text value ("Tue Jan 18 10:10:57 2011") you provided for [AlarmStart].

? Format(Mid("Tue Jan 18 10:10:57 2011", 5, 7) & _
    Right("Tue Jan 18 10:10:57 2011", 4), "mm/dd/yyyy")
01/18/2011

edited Aug 31 '11 at 14:43

answered Aug 29 '11 at 15:59

HansUp

95,961
11
77
135

So i've set up the format like this `? format(Mid([alarmdet].[AlarmStart],5,7) & right([alarmdet]![alarmstart], 4), "mm/dd/yyyy")` although, i get this **01-18-2011** Any thoughts? – James213 Aug 29 '11 at 19:47
@James213 Show us a sample value for [AlarmStart] and how you want that value displayed. Also, is the data type of [AlarmStart] text or Date/Time? – HansUp Aug 29 '11 at 20:39
`Tue Jan 18 10:10:57 2011` is an example of what i get from [AlarmStart], what I need to pull out is the Jan 18 2001 and have it look like `1/18/2011` currently with the format set to "mm/dd/yyyy" i get this `01-18-2011` and it is a text data type. – James213 Aug 31 '11 at 14:17
On my system, I get what I think you're looking for. I don't understand why it's working differently on your system. – HansUp Aug 31 '11 at 14:44

mismatched datatype in access query

2 Answers2

Linked