mismatched datatype in access query

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 quer开发者_Go百科y 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.

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

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