访问查询中的不匹配数据类型 [英] mismatched datatype in access query

问题描述

所以我有一个查询，假设从两个表中获取我的数据。只要日期格式在英语（美国）区域设置下，它工作正常。当它在国际格式化时，查询在运行时失败并且失败。查询是

  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，
 AlarmStart，12,2）AS AlarmHH，
 Mid（alarmdet.AlarmStart，15,2）AS AlarmMM，
 Mid（alarmdet.AlarmStart，18,2）AS AlarmSS，
 CVDate中间（[alarmdet]。[AlarmStart]，5,7）& Right（[alarmdet]。[AlarmStart]，4）））AS AlarmDate，Alarms.ALARM_DESC 
 FROM AlarmDet INNER JOIN Alarm AlarmDet.AlarmId =警报; 
  

查询中的公式是采用alarmstart（date）并将其拆分成独立的段。

当我进入并尝试运行它时导致错误的部分是：

  CVDate（（Mid（[alarmdet]。[AlarmStart]，5,7）& Right（[alarmdet]。[AlarmStart]，4） $ b  

我想知道的是，如果有一种方法在访问中转换查询的形式被转入美国的日期格式？
或者有更好的方法来构造查询的这部分，以使其在国际格式下运行吗？

更新

<$>

<$> p $ p> 格式（Mid（[alarmdet]。[AlarmStart]，5,7）& right（[alarmdet]！[alarmstart]，4），mm / dd / yyyy）


但是，不是得到mm / dd / yyyy，我得到：
mm-dd -yyyy。

有任何想法，为什么会这样？

无论如何，这与您的区域设置所在位置有关。

<非常感谢任何建议或帮助。

感谢。

解决方案

p>查看Format（）函数是否允许您根据需要显示[AlarmStart]。下面是从立即窗口复制的一些示例表达式。

 ？格式（日期（），yyyy / mm / dd）
 2011/08/29 
 
？格式（日期（），mmm d，yyyy h：nn：ss ampm）
 2011年8月29日上午12:00:00 
  

编辑：这是我在即时窗口中使用您提供的示例文本值（Tue Jan 18 10:10:57 2011） [AlarmStart]。

 ？格式（Mid（Tue Jan 18 10:10:57 2011，5，7）& _ 
右（Tue Jan 18 10:10:57 2011，4），mm / dd / yyyy ）
 01/18/2011 
  

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.

解决方案

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

这篇关于访问查询中的不匹配数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！