DLOOKUP函数以mm / dd / yyyy的格式取代日期，而不是dd / mm / yyyy [英] DLOOKUP function takes date in the format of mm/dd/yyyy instead of dd/mm/yyyy

问题描述

我在表格中使用以下DLOOKUP功能来查找值 

= Round（DLookUp（" [ADV]"，" [ADV] "，" [ALPHA] ='"& [ALPHA CODE]&"'AND [RepDt] =＃"& [COMPROFILEDT]&"＃"）/ 100000,2）

如果日期大于12，它可以正常工作但如果日期超过12则不会给出期望的结果

例如

日期为2017年1月31日，2017年2月17日它给了我正确的结果，但是对于日期03/02/17即第3次17和10/02/17，即10月17日它没有给出我的结果因为它预示着10月日期的系统搜索在
表中不可用

我如何纠正这个问题。请帮助

Manoj Sh 

解决方案

您需要将日期格式化为Access期望的格式。

< pre class ="prettyprint lang-vb"> = Round（DLookUp（"[ADV]"，"[ADV]"，"[ALPHA] ='"& [ALPHA CODE]&" 'AND [RepDt] ="& Format

（[COMPROFILEDT]，" \ #mm\ / dd \ / yyyy \＃"））/ 100000 ，2）

或者更好的是添加Allen Browne的
SQLDate函数到您的保留曲目并在将日期值传递给查询和函数时调用它。

 = Round（DLookUp（" [ADV]" ;，"[ADV]"，"[ALPHA] ='"& [ALPHA CODE]&"'AND [RepDt] ="& SQLDate（[COMPROFILEDT]））/ 100000,2 ）

I am using following DLOOKUP function in my form to look up a value 

=Round(DLookUp("[ADV]","[ADV]","[ALPHA] = '" & [ALPHA CODE] & "' AND [RepDt] = #" & [COMPROFILEDT] & "#")/100000,2)

it works fine if date is above 12 but does not give desired result if date exceed 12

for example

dates for 31/01/2017, 17/02/2017 it gives me correct result however for dates 03/02/17 i.e. 3rd feb 17 and 10/02/17 i.e. 10 th feb 17 it does not give my the result as it appers that system search of the dates of october month which is not available in the table

How can i rectify the problem. Please help

Manoj Sh 

解决方案

You need to format the dates into the format Access expects.

=Round(DLookUp("[ADV]","[ADV]","[ALPHA] = '" & [ALPHA CODE] & "' AND [RepDt] = " & Format

([COMPROFILEDT], "\#mm\/dd\/yyyy\#"))/100000,2)

or better yet add Allen Browne's SQLDate Function to your repertoire and call it whenever passing date values to queries and functions.

=Round(DLookUp("[ADV]","[ADV]","[ALPHA] = '" & [ALPHA CODE] & "' AND [RepDt] = " & SQLDate([COMPROFILEDT]))/100000,2)

这篇关于DLOOKUP函数以mm / dd / yyyy的格式取代日期，而不是dd / mm / yyyy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！