Warning of duplicate value using dlookup with before update proper

[Guest]

Hello:

Greetings to all.

I am puzzle with an inconsistant result from dlookup, warning of “Duplicated
Value†in a form control “before_update†property. I am creating data entry
forms for different work areas. The sub worked for one area but not the
other.

The one based on a combined fields of Date and Period (Time) worked, as
written below. The domain is queryDatePeriod and the current form is
formarea_1.

Private Sub Period_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Exit_this_sub

Dim x As Variant

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

Exit_this_sub:
Exit Sub

Err_Exit_this_sub:
MsgBox Error$
Resume Exit_this_sub

End Sub

However, it DID NOT work when I used the "same" sub (Private Sub
Date_BeforeUpdate(Cancel As Integer) with different “x†expression for
another area. The expression is as stated below. It used a combined fields of
Shift and Date. The domain is queryShiftDate and the current form is now
formarea_2.

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I multiple checked the table and form properties. Between the tables and
forms, they have the same property types with the fields and controls in
concern.

Yet, the Warning “Duplicate Value†did not shown in the second case as the
control loses its focus. And per debug window in Access VBE, this fouled
expression is “out of contextâ€.

What is “out of context�

I substituted the dllokup criteria with a real value from queryShiftDate as
shift one & date (116/03/2005), the debug return “nullâ€.

Why??? It is there. But dlookup did not detect it. Sleepless nights.

Thank you in advance for any suggestion. I tried with Access 97 and 2000.


James

 

[John Nurick]

Hi James,

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I think the first thing to do is to remove all possible ambiguity from
these expressions.

1) Using field names that are the same as common properties or function
names - e.g. "Date" - sooner or later causes confusion or worse.

2) In the WHERE argument of the DLookups, you explicitly specify the
form name and control name for one part of the string you're
concatenating but not the other:
Forms![ formarea_1]![Date]
but
[period]
and
Forms![ formarea _2]![Shift]
but
[Date]
.. I for one am not game to predict what that reference to [Date] will
return in all circumstances.

3) If you're concatenating unknown values in order to compare them with
another concatenation of unknown values, you should make certain that
the values will always be formatted consistently. At present, if you
have shift 1 and the date is 11/21/2005 you'll get the same result as
shift 11 and 1/21/2005 - which probably isn't what you want. So it would
be better to use something like
Format([shift],"00") & "#" & Format([DateField], "mm/dd/yyyy") & "#"

4) There's actually no need to concatenate the values in order to
compare them in the WHERE argument of DLookup(). Use something like

..., "[DateField]=" & CDate(Forms!FormName!ControlName) _
& " AND [period]=" & Forms!FormName!OtherControlName )

Hello:

Greetings to all.

I am puzzle with an inconsistant result from dlookup, warning of “Duplicated
Value” in a form control “before_update” property. I am creating data entry
forms for different work areas. The sub worked for one area but not the
other.

The one based on a combined fields of Date and Period (Time) worked, as
written below. The domain is queryDatePeriod and the current form is
formarea_1.

Private Sub Period_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Exit_this_sub

Dim x As Variant

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

Exit_this_sub:
Exit Sub

Err_Exit_this_sub:
MsgBox Error$
Resume Exit_this_sub

End Sub

However, it DID NOT work when I used the "same" sub (Private Sub
Date_BeforeUpdate(Cancel As Integer) with different “x” expression for
another area. The expression is as stated below. It used a combined fields of
Shift and Date. The domain is queryShiftDate and the current form is now
formarea_2.

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I multiple checked the table and form properties. Between the tables and
forms, they have the same property types with the fields and controls in
concern.

Yet, the Warning “Duplicate Value” did not shown in the second case as the
control loses its focus. And per debug window in Access VBE, this fouled
expression is “out of context”.

What is “out of context”?

I substituted the dllokup criteria with a real value from queryShiftDate as
shift one & date (116/03/2005), the debug return “null”.

Why??? It is there. But dlookup did not detect it. Sleepless nights.

Thank you in advance for any suggestion. I tried with Access 97 and 2000.


James

 

[Guest]

Thanks John:

Your insights were very meaningful. I modified my "date" field to DateField.
I also tested the dlookup in the debug window with # to delimit the date of
"real" value. It worked. It found the date.

However, when I went back to use the ControlName. I got stuck again. It came
back null.

As you suggested, to keep it simple. I now compare only the "dates", the one
in the query vs the one on the form, with Cdate and without CDate.

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]=" &
CDate(Forms! _
[formarea _2]![DateField]))

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]= '" & Forms!
_[frmydcComments_1]![DateField] & "'")

Is there still a referrence inconsistancy in the WHERE argument? Thank you.

james

Hi James,

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I think the first thing to do is to remove all possible ambiguity from
these expressions.

1) Using field names that are the same as common properties or function
names - e.g. "Date" - sooner or later causes confusion or worse.

2) In the WHERE argument of the DLookups, you explicitly specify the
form name and control name for one part of the string you're
concatenating but not the other:
Forms![ formarea_1]![Date]
but
[period]
and
Forms![ formarea _2]![Shift]
but
[Date]
.. I for one am not game to predict what that reference to [Date] will
return in all circumstances.

3) If you're concatenating unknown values in order to compare them with
another concatenation of unknown values, you should make certain that
the values will always be formatted consistently. At present, if you
have shift 1 and the date is 11/21/2005 you'll get the same result as
shift 11 and 1/21/2005 - which probably isn't what you want. So it would
be better to use something like
Format([shift],"00") & "#" & Format([DateField], "mm/dd/yyyy") & "#"

4) There's actually no need to concatenate the values in order to
compare them in the WHERE argument of DLookup(). Use something like

..., "[DateField]=" & CDate(Forms!FormName!ControlName) _
& " AND [period]=" & Forms!FormName!OtherControlName )

Hello:

Greetings to all.

I am puzzle with an inconsistant result from dlookup, warning of “Duplicated
Value†in a form control “before_update†property. I am creating data entry
forms for different work areas. The sub worked for one area but not the
other.

The one based on a combined fields of Date and Period (Time) worked, as
written below. The domain is queryDatePeriod and the current form is
formarea_1.

Private Sub Period_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Exit_this_sub

Dim x As Variant

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

Exit_this_sub:
Exit Sub

Err_Exit_this_sub:
MsgBox Error$
Resume Exit_this_sub

End Sub

However, it DID NOT work when I used the "same" sub (Private Sub
Date_BeforeUpdate(Cancel As Integer) with different “x†expression for
another area. The expression is as stated below. It used a combined fields of
Shift and Date. The domain is queryShiftDate and the current form is now
formarea_2.

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I multiple checked the table and form properties. Between the tables and
forms, they have the same property types with the fields and controls in
concern.

Yet, the Warning “Duplicate Value†did not shown in the second case as the
control loses its focus. And per debug window in Access VBE, this fouled
expression is “out of contextâ€.

What is “out of context�

I substituted the dllokup criteria with a real value from queryShiftDate as
shift one & date (116/03/2005), the debug return “nullâ€.

Why??? It is there. But dlookup did not detect it. Sleepless nights.

Thank you in advance for any suggestion. I tried with Access 97 and 2000.


James

 

[John Vinson]

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]=" &
CDate(Forms! _
[formarea _2]![DateField]))

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]= '" & Forms!
_[frmydcComments_1]![DateField] & "'")

Is there still a referrence inconsistancy in the WHERE argument? Thank you.

Yes: date criteria must be delimited by #. Try

x = DLookup("[dateField]", "[queryShiftDate]", "[datefield]= #" & _
Forms![frmydcComments_1]![DateField] & "#")


John W. Vinson[MVP]

 

[Andreas]

My solution would be:

x = DLookup("[DateField]", "[queryShiftDate]", "[datefield]=#" &
Format(Forms![formarea _2]![DateField],"dd-mmm-yyyy") & "#")

Untested.
(also note: I have removed the space in [ queryShiftDate], not sure on
space in [formarea _2], either way - life is easier without spaces)

Regards,
Andreas

Thanks John:

Your insights were very meaningful. I modified my "date" field to DateField.
I also tested the dlookup in the debug window with # to delimit the date of
"real" value. It worked. It found the date.

However, when I went back to use the ControlName. I got stuck again. It came
back null.

As you suggested, to keep it simple. I now compare only the "dates", the one
in the query vs the one on the form, with Cdate and without CDate.

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]=" &
CDate(Forms! _
[formarea _2]![DateField]))

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]= '" & Forms!
_[frmydcComments_1]![DateField] & "'")

Is there still a referrence inconsistancy in the WHERE argument? Thank you.

james

:

Hi James,

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I think the first thing to do is to remove all possible ambiguity from
these expressions.

1) Using field names that are the same as common properties or function
names - e.g. "Date" - sooner or later causes confusion or worse.

2) In the WHERE argument of the DLookups, you explicitly specify the
form name and control name for one part of the string you're
concatenating but not the other:
Forms![ formarea_1]![Date]
but
[period]
and
Forms![ formarea _2]![Shift]
but
[Date]
.. I for one am not game to predict what that reference to [Date] will
return in all circumstances.

3) If you're concatenating unknown values in order to compare them with
another concatenation of unknown values, you should make certain that
the values will always be formatted consistently. At present, if you
have shift 1 and the date is 11/21/2005 you'll get the same result as
shift 11 and 1/21/2005 - which probably isn't what you want. So it would
be better to use something like
Format([shift],"00") & "#" & Format([DateField], "mm/dd/yyyy") & "#"

4) There's actually no need to concatenate the values in order to
compare them in the WHERE argument of DLookup(). Use something like

..., "[DateField]=" & CDate(Forms!FormName!ControlName) _
& " AND [period]=" & Forms!FormName!OtherControlName )

Hello:

Greetings to all.

I am puzzle with an inconsistant result from dlookup, warning of “Duplicated
Value†in a form control “before_update†property. I am creating data entry
forms for different work areas. The sub worked for one area but not the
other.

The one based on a combined fields of Date and Period (Time) worked, as
written below. The domain is queryDatePeriod and the current form is
formarea_1.

Private Sub Period_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Exit_this_sub

Dim x As Variant

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

Exit_this_sub:
Exit Sub

Err_Exit_this_sub:
MsgBox Error$
Resume Exit_this_sub

End Sub

However, it DID NOT work when I used the "same" sub (Private Sub
Date_BeforeUpdate(Cancel As Integer) with different “x†expression for
another area. The expression is as stated below. It used a combined fields of
Shift and Date. The domain is queryShiftDate and the current form is now
formarea_2.

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I multiple checked the table and form properties. Between the tables and
forms, they have the same property types with the fields and controls in
concern.

Yet, the Warning “Duplicate Value†did not shown in the second case as the
control loses its focus. And per debug window in Access VBE, this fouled
expression is “out of contextâ€.

What is “out of context�

I substituted the dllokup criteria with a real value from queryShiftDate as
shift one & date (116/03/2005), the debug return “nullâ€.

Why??? It is there. But dlookup did not detect it. Sleepless nights.

Thank you in advance for any suggestion. I tried with Access 97 and 2000.


James

 

[Guest]

Hello John, Andreas:

I tried the dlookup at home with Access 97. Do not have access 2000 till I
return to work. It still didn't give me the warning of duplicate date.

I also made a db1.mdb with test table, qrytest and frmtest. It has 3
columns, DateField, Shift and TimeField. Result still was negative. See below
copy from debug window.

?x = DLookup("[dateField]", "[qrytest]", "[datefield]= #" & _
Forms![frmtest]![DateField] & "#")
False
?x = DLookup("[DateField]", "[qrytest]", "[datefield]=#" & _
Format(Forms![frmtest]![DateField], "dd-mm-yyyy") & "#")
False
?Dcount("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
2
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
15/03/2005

Suggestions. Thanks.

james

My solution would be:

x = DLookup("[DateField]", "[queryShiftDate]", "[datefield]=#" &
Format(Forms![formarea _2]![DateField],"dd-mmm-yyyy") & "#")

Untested.
(also note: I have removed the space in [ queryShiftDate], not sure on
space in [formarea _2], either way - life is easier without spaces)

Regards,
Andreas

Thanks John:

Your insights were very meaningful. I modified my "date" field to DateField.
I also tested the dlookup in the debug window with # to delimit the date of
"real" value. It worked. It found the date.

However, when I went back to use the ControlName. I got stuck again. It came
back null.

As you suggested, to keep it simple. I now compare only the "dates", the one
in the query vs the one on the form, with Cdate and without CDate.

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]=" &
CDate(Forms! _
[formarea _2]![DateField]))

x = DLookup("[dateField]", "[ queryShiftDate]", "[datefield]= '" & Forms!
_[frmydcComments_1]![DateField] & "'")

Is there still a referrence inconsistancy in the WHERE argument? Thank you.

james

:

Hi James,


x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")


x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I think the first thing to do is to remove all possible ambiguity from
these expressions.

1) Using field names that are the same as common properties or function
names - e.g. "Date" - sooner or later causes confusion or worse.

2) In the WHERE argument of the DLookups, you explicitly specify the
form name and control name for one part of the string you're
concatenating but not the other:
Forms![ formarea_1]![Date]
but
[period]
and
Forms![ formarea _2]![Shift]
but
[Date]
.. I for one am not game to predict what that reference to [Date] will
return in all circumstances.

3) If you're concatenating unknown values in order to compare them with
another concatenation of unknown values, you should make certain that
the values will always be formatted consistently. At present, if you
have shift 1 and the date is 11/21/2005 you'll get the same result as
shift 11 and 1/21/2005 - which probably isn't what you want. So it would
be better to use something like
Format([shift],"00") & "#" & Format([DateField], "mm/dd/yyyy") & "#"

4) There's actually no need to concatenate the values in order to
compare them in the WHERE argument of DLookup(). Use something like

..., "[DateField]=" & CDate(Forms!FormName!ControlName) _
& " AND [period]=" & Forms!FormName!OtherControlName )




On Thu, 17 Mar 2005 12:59:03 -0800, "james"


Hello:

Greetings to all.

I am puzzle with an inconsistant result from dlookup, warning of “Duplicated
Value†in a form control “before_update†property. I am creating data entry
forms for different work areas. The sub worked for one area but not the
other.

The one based on a combined fields of Date and Period (Time) worked, as
written below. The domain is queryDatePeriod and the current form is
formarea_1.

Private Sub Period_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Exit_this_sub

Dim x As Variant

x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

Exit_this_sub:
Exit Sub

Err_Exit_this_sub:
MsgBox Error$
Resume Exit_this_sub

End Sub

However, it DID NOT work when I used the "same" sub (Private Sub
Date_BeforeUpdate(Cancel As Integer) with different “x†expression for
another area. The expression is as stated below. It used a combined fields of
Shift and Date. The domain is queryShiftDate and the current form is now
formarea_2.

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I multiple checked the table and form properties. Between the tables and
forms, they have the same property types with the fields and controls in
concern.

Yet, the Warning “Duplicate Value†did not shown in the second case as the
control loses its focus. And per debug window in Access VBE, this fouled
expression is “out of contextâ€.

What is “out of context�

I substituted the dllokup criteria with a real value from queryShiftDate as
shift one & date (116/03/2005), the debug return “nullâ€.

Why??? It is there. But dlookup did not detect it. Sleepless nights.

Thank you in advance for any suggestion. I tried with Access 97 and 2000.


James

 

[John Vinson]

Hello John, Andreas:

I tried the dlookup at home with Access 97. Do not have access 2000 till I
return to work. It still didn't give me the warning of duplicate date.

I also made a db1.mdb with test table, qrytest and frmtest. It has 3
columns, DateField, Shift and TimeField. Result still was negative. See below
copy from debug window.

?x = DLookup("[dateField]", "[qrytest]", "[datefield]= #" & _
Forms![frmtest]![DateField] & "#")
False
?x = DLookup("[DateField]", "[qrytest]", "[datefield]=#" & _
Format(Forms![frmtest]![DateField], "dd-mm-yyyy") & "#")
False
?Dcount("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
2
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
15/03/2005

If you're searching for a literal date you MUST!! - no option - use
either the US month-day-year format, or an unambiguous format such as
15-Mar-2005. dd-mm-yyyy format *will not work*.

I'd ust the second option, with the Format() function, but use
mm/dd/yyyy as the format.

Note also that if the table field contains both a date and a time
portion, the dlookup will NOT find it. #15-Mar-2005 11:32:12# is not
the same value as #15-Mar-2005# (which is treated as being midnight at
the start of that day).

John W. Vinson[MVP]

 

[Guest]

Thanks John about the U.S. or format function. I will try that. With respect
to the date, I think there were no decimal points. It was generated by Date()
in the "Short Date -> dd/mm/yyyy" format.

I tried this afternoon to change it to the date portion of the double number
for comparison but till now I still could not finger out the delimit syntax
that goes with ControlName.

Also, I found out if I were to omit the "x=" in the dlookup (debug window),
I got positive result. But once I put "x=" back in, the resuly was "false".

Copy from the debug window:

? Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
2
?x = Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
20/03/2005
?x = Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False

?CDbl(#20/03/2005#)
38431
?CDbl(Forms![frmtest]![DateField])
38431
?CDbl("# Forms![frmtest]![DateField] #" )
?CDbl("#" & Forms![frmtest]![DateField] & "#" )
?CDbl("# & Forms![frmtest]![DateField] & #" )


james

Hello John, Andreas:

I tried the dlookup at home with Access 97. Do not have access 2000 till I
return to work. It still didn't give me the warning of duplicate date.

I also made a db1.mdb with test table, qrytest and frmtest. It has 3
columns, DateField, Shift and TimeField. Result still was negative. See below
copy from debug window.

?x = DLookup("[dateField]", "[qrytest]", "[datefield]= #" & _
Forms![frmtest]![DateField] & "#")
False
?x = DLookup("[DateField]", "[qrytest]", "[datefield]=#" & _
Format(Forms![frmtest]![DateField], "dd-mm-yyyy") & "#")
False
?Dcount("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
2
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
15/03/2005

If you're searching for a literal date you MUST!! - no option - use
either the US month-day-year format, or an unambiguous format such as
15-Mar-2005. dd-mm-yyyy format *will not work*.

I'd ust the second option, with the Format() function, but use
mm/dd/yyyy as the format.

Note also that if the table field contains both a date and a time
portion, the dlookup will NOT find it. #15-Mar-2005 11:32:12# is not
the same value as #15-Mar-2005# (which is treated as being midnight at
the start of that day).

John W. Vinson[MVP]

 

[Guest]

Hello:

Thank you for your patience and assistance.

I managed to work around the CDbl(). Instead of dealing with different date
formats, I work with the interger numbers. I created another control in the
form with its source equals to CDbl(DateField). So, I work with numbers
instead of dates. But I have difficulty in troublshooting the x=dlookup ...
or the x=dcount ...

Why is it that once I put "x=", the return becomes negative???

Challenge one after another... from the debug window:

? x=Dcount("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
False
? Dcount("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
1
? x=Dlookup("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
False
? Dlookup("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
38431

Thanks.

james

Thanks John about the U.S. or format function. I will try that. With respect
to the date, I think there were no decimal points. It was generated by Date()
in the "Short Date -> dd/mm/yyyy" format.

I tried this afternoon to change it to the date portion of the double number
for comparison but till now I still could not finger out the delimit syntax
that goes with ControlName.

Also, I found out if I were to omit the "x=" in the dlookup (debug window),
I got positive result. But once I put "x=" back in, the resuly was "false".

Copy from the debug window:

? Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
2
?x = Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
20/03/2005
?x = Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False

?CDbl(#20/03/2005#)
38431
?CDbl(Forms![frmtest]![DateField])
38431
?CDbl("# Forms![frmtest]![DateField] #" )
?CDbl("#" & Forms![frmtest]![DateField] & "#" )
?CDbl("# & Forms![frmtest]![DateField] & #" )


james

Hello John, Andreas:

I tried the dlookup at home with Access 97. Do not have access 2000 till I
return to work. It still didn't give me the warning of duplicate date.

I also made a db1.mdb with test table, qrytest and frmtest. It has 3
columns, DateField, Shift and TimeField. Result still was negative. See below
copy from debug window.

?x = DLookup("[dateField]", "[qrytest]", "[datefield]= #" & _
Forms![frmtest]![DateField] & "#")
False
?x = DLookup("[DateField]", "[qrytest]", "[datefield]=#" & _
Format(Forms![frmtest]![DateField], "dd-mm-yyyy") & "#")
False
?Dcount("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
2
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
15/03/2005

If you're searching for a literal date you MUST!! - no option - use
either the US month-day-year format, or an unambiguous format such as
15-Mar-2005. dd-mm-yyyy format *will not work*.

I'd ust the second option, with the Format() function, but use
mm/dd/yyyy as the format.

Note also that if the table field contains both a date and a time
portion, the dlookup will NOT find it. #15-Mar-2005 11:32:12# is not
the same value as #15-Mar-2005# (which is treated as being midnight at
the start of that day).

John W. Vinson[MVP]

 

[John Vinson]

Thanks John about the U.S. or format function. I will try that. With respect
to the date, I think there were no decimal points. It was generated by Date()
in the "Short Date -> dd/mm/yyyy" format.

There is a decimal point. The format IS COMPLETELY IRRELEVANT. No
matter what the format of a date field is, a Date/Time value is stored
as a Double Float count of days and fractions of a day since midnight,
December 30, 1899. If you use Date() as the source, then the time
(fractional) part will be zero so you should be safe.

I tried this afternoon to change it to the date portion of the double number
for comparison but till now I still could not finger out the delimit syntax
that goes with ControlName.

There's no point to changing the value if you used Date(). The date
will be displayed in whatever format you've defined in your Regional
settings.

Also, I found out if I were to omit the "x=" in the dlookup (debug window),
I got positive result. But once I put "x=" back in, the resuly was "false".

That's because the ? means "show me the value of this expression"; if
the expression is

x = DCount(....

then the expression will be True if x is equal to the count of records
which meet that criterion, False otherwise.

It looks like the DCount is returning - at least - *reasonable* values
(2 means that there are two records in that date range).

John W. Vinson[MVP]

 

[John Vinson]

Why is it that once I put "x=", the return becomes negative???

As noted elsewhere in the thread - because you're TRYING TOO HARD.

The ? means "show me the value of this expression".

If you were to type

? 1 = 2

the answer would be False or (equivalently) 0.

If you were to type

?2 = 2

the answer would be True or -1.


John W. Vinson[MVP]

 

[Andreas]

Just a short note.
If you look in my original post, I used 3 m's, not 2 like you did.
This is exactly the point of using the Format function - to create an
unambiguous format.
Living in a non-US-format country, I have had all sorts of fun with this
(NOT).
Using the Format function, you will never have to worry about what
format the date is supposed to be in, as the requirements will change
depending on what you are doing (SQL, Functions, VBA, etc).
Also, by ALWAYS using the dd-mmm-yyyy format, it doesn't matter who
reads this where, it will always be unambiguous - at least until we get
to the Y10K bug

Regards,
Andreas

Thanks John about the U.S. or format function. I will try that. With respect
to the date, I think there were no decimal points. It was generated by Date()
in the "Short Date -> dd/mm/yyyy" format.

I tried this afternoon to change it to the date portion of the double number
for comparison but till now I still could not finger out the delimit syntax
that goes with ControlName.

Also, I found out if I were to omit the "x=" in the dlookup (debug window),
I got positive result. But once I put "x=" back in, the resuly was "false".

Copy from the debug window:

? Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
2
?x = Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
20/03/2005
?x = Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False

?CDbl(#20/03/2005#)
38431
?CDbl(Forms![frmtest]![DateField])
38431
?CDbl("# Forms![frmtest]![DateField] #" )
?CDbl("#" & Forms![frmtest]![DateField] & "#" )
?CDbl("# & Forms![frmtest]![DateField] & #" )


james



:

Hello John, Andreas:

I tried the dlookup at home with Access 97. Do not have access 2000 till I
return to work. It still didn't give me the warning of duplicate date.

I also made a db1.mdb with test table, qrytest and frmtest. It has 3
columns, DateField, Shift and TimeField. Result still was negative. See below
copy from debug window.

?x = DLookup("[dateField]", "[qrytest]", "[datefield]= #" & _
Forms![frmtest]![DateField] & "#")
False
?x = DLookup("[DateField]", "[qrytest]", "[datefield]=#" & _
Format(Forms![frmtest]![DateField], "dd-mm-yyyy") & "#")
False
?Dcount("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
2
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
15/03/2005

If you're searching for a literal date you MUST!! - no option - use
either the US month-day-year format, or an unambiguous format such as
15-Mar-2005. dd-mm-yyyy format *will not work*.

I'd ust the second option, with the Format() function, but use
mm/dd/yyyy as the format.

Note also that if the table field contains both a date and a time
portion, the dlookup will NOT find it. #15-Mar-2005 11:32:12# is not
the same value as #15-Mar-2005# (which is treated as being midnight at
the start of that day).

John W. Vinson[MVP]

 

[Andreas]

? x=Dcount("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
False

You are comparing x to the return value of the DCount.

? Dcount("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
1

This is telling you that the DCount found 1 record with this value.

? x=Dlookup("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
False

You are comparing x to the return value of the DLookup.

? Dlookup("[dateNumber]", "[qrytest]", "[dateNumber]=
Forms![frmtest]![DateNumber]")
38431

The returned value (first value matching the criteria)


Regards,
Andreas

 

[Guest]

Thanks. until I return to work next week, I wouldn't be able to try out on
the Y2K version. Yes, there were 3 m's. Just that I didn't know its
significance then.

Cheers.

james

Just a short note.
If you look in my original post, I used 3 m's, not 2 like you did.
This is exactly the point of using the Format function - to create an
unambiguous format.
Living in a non-US-format country, I have had all sorts of fun with this
(NOT).
Using the Format function, you will never have to worry about what
format the date is supposed to be in, as the requirements will change
depending on what you are doing (SQL, Functions, VBA, etc).
Also, by ALWAYS using the dd-mmm-yyyy format, it doesn't matter who
reads this where, it will always be unambiguous - at least until we get
to the Y10K bug

Regards,
Andreas

Thanks John about the U.S. or format function. I will try that. With respect
to the date, I think there were no decimal points. It was generated by Date()
in the "Short Date -> dd/mm/yyyy" format.

I tried this afternoon to change it to the date portion of the double number
for comparison but till now I still could not finger out the delimit syntax
that goes with ControlName.

Also, I found out if I were to omit the "x=" in the dlookup (debug window),
I got positive result. But once I put "x=" back in, the resuly was "false".

Copy from the debug window:

? Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
2
?x = Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
20/03/2005
?x = Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False

?CDbl(#20/03/2005#)
38431
?CDbl(Forms![frmtest]![DateField])
38431
?CDbl("# Forms![frmtest]![DateField] #" )
?CDbl("#" & Forms![frmtest]![DateField] & "#" )
?CDbl("# & Forms![frmtest]![DateField] & #" )


james



:

On Sun, 20 Mar 2005 12:09:02 -0800, "james"


Hello John, Andreas:

I tried the dlookup at home with Access 97. Do not have access 2000 till I
return to work. It still didn't give me the warning of duplicate date.

I also made a db1.mdb with test table, qrytest and frmtest. It has 3
columns, DateField, Shift and TimeField. Result still was negative. See below
copy from debug window.

?x = DLookup("[dateField]", "[qrytest]", "[datefield]= #" & _
Forms![frmtest]![DateField] & "#")
False
?x = DLookup("[DateField]", "[qrytest]", "[datefield]=#" & _
Format(Forms![frmtest]![DateField], "dd-mm-yyyy") & "#")
False
?Dcount("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
2
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
15/03/2005

If you're searching for a literal date you MUST!! - no option - use
either the US month-day-year format, or an unambiguous format such as
15-Mar-2005. dd-mm-yyyy format *will not work*.

I'd ust the second option, with the Format() function, but use
mm/dd/yyyy as the format.

Note also that if the table field contains both a date and a time
portion, the dlookup will NOT find it. #15-Mar-2005 11:32:12# is not
the same value as #15-Mar-2005# (which is treated as being midnight at
the start of that day).

John W. Vinson[MVP]

 

[Guest]

Hello Andreas, john:

Good Day. I tried it out at work this week. It worked fine. Thank you for
explaining. Best regards.

james

Thanks. until I return to work next week, I wouldn't be able to try out on
the Y2K version. Yes, there were 3 m's. Just that I didn't know its
significance then.

Cheers.

james

Just a short note.
If you look in my original post, I used 3 m's, not 2 like you did.
This is exactly the point of using the Format function - to create an
unambiguous format.
Living in a non-US-format country, I have had all sorts of fun with this
(NOT).
Using the Format function, you will never have to worry about what
format the date is supposed to be in, as the requirements will change
depending on what you are doing (SQL, Functions, VBA, etc).
Also, by ALWAYS using the dd-mmm-yyyy format, it doesn't matter who
reads this where, it will always be unambiguous - at least until we get
to the Y10K bug

Regards,
Andreas

Thanks John about the U.S. or format function. I will try that. With respect
to the date, I think there were no decimal points. It was generated by Date()
in the "Short Date -> dd/mm/yyyy" format.

I tried this afternoon to change it to the date portion of the double number
for comparison but till now I still could not finger out the delimit syntax
that goes with ControlName.

Also, I found out if I were to omit the "x=" in the dlookup (debug window),
I got positive result. But once I put "x=" back in, the resuly was "false".

Copy from the debug window:

? Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
2
?x = Dcount("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
20/03/2005
?x = Dlookup("[dateField]", "[qrytest]", "[datefield]= #" &
Forms![frmtest]![DateField]& "#" )
False

?CDbl(#20/03/2005#)
38431
?CDbl(Forms![frmtest]![DateField])
38431
?CDbl("# Forms![frmtest]![DateField] #" )
?CDbl("#" & Forms![frmtest]![DateField] & "#" )
?CDbl("# & Forms![frmtest]![DateField] & #" )


james



:


On Sun, 20 Mar 2005 12:09:02 -0800, "james"


Hello John, Andreas:

I tried the dlookup at home with Access 97. Do not have access 2000 till I
return to work. It still didn't give me the warning of duplicate date.

I also made a db1.mdb with test table, qrytest and frmtest. It has 3
columns, DateField, Shift and TimeField. Result still was negative. See below
copy from debug window.

?x = DLookup("[dateField]", "[qrytest]", "[datefield]= #" & _
Forms![frmtest]![DateField] & "#")
False
?x = DLookup("[DateField]", "[qrytest]", "[datefield]=#" & _
Format(Forms![frmtest]![DateField], "dd-mm-yyyy") & "#")
False
?Dcount("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
2
?Dlookup("[dateField]", "[qrytest]", "[datefield]= #15/03/2005#")
15/03/2005

If you're searching for a literal date you MUST!! - no option - use
either the US month-day-year format, or an unambiguous format such as
15-Mar-2005. dd-mm-yyyy format *will not work*.

I'd ust the second option, with the Format() function, but use
mm/dd/yyyy as the format.

Note also that if the table field contains both a date and a time
portion, the dlookup will NOT find it. #15-Mar-2005 11:32:12# is not
the same value as #15-Mar-2005# (which is treated as being midnight at
the start of that day).

John W. Vinson[MVP]