DateSerial and DLookup

[Guest]

Hi all


TblClients
CD1stName = Text
CDSurname = Text
CDDateofBirth = Time/Date


The form is feb by a query and the date of birth used in the form is …..

CDDateOfBirth:
DateSerial(qryOnLineBookingsUKOP_QueryFeeder!Personal_DOBYear,qryOnLineBookingsUKOP_QueryFeeder!Personal_DOBMonth,qryOnLineBookingsUKOP_QueryFeeder!Personal_DOBDay)

Dim strID As String
strID = Nz(DLookup("[CDClientID]", "[TblClients]", "[CD1stName] ='"
& Form!txt1stName & "' AND [CDSurname] = '" & Form!txtSurname & "'AND
[CDDateofBirth] = '" #& Form!txtDateofBirth &# "'"), "0")

If strID = "0" Then
Etc
Etc


This “will†find records – one’s that were type in on a key board. Also if
the date of birth is taken out of the criteria it works fine – so I guess the
problem is the date of birth and how it being constructed. (DateSerial).

I just need to find out if a record already exists and if it does ..do one
thing. If it doesn't .. do something else.

I can post the whole button code if need but it's quite long.

If anyone understands the above (is the syntax correct ?) and can give any
tips it would be “really†helpful (been looking this for hours).

Many thanks

 

[Guest]

Never mind - I will come back to it next year
Will use the postcode for now

Many thanks

 

[Douglas J. Steele]

I don't see how that can even compile, much less work, since your use of the
# delimiter is wrong. As well, Form!txtDateofBirth appears to be missing the
name of the form:

strID = Nz(DLookup("[CDClientID]", "[TblClients]", "[CD1stName] ='" & _
Form!txt1stName & "' AND [CDSurname] = '" & Form!txtSurname & _
"'AND [CDDateofBirth] = " & _
Format(Form!NameOfForm!txtDateofBirth, "\#yyyy\-mm\-dd\#")), "0")

Since you're in the UK, I suspect people are typing dates in dd/mm/yyyy
format. Just as in SQL statements, Access will not respect that format in
DLookup statements: it will always treat the date as mm/dd/yyyy unless it's
after the 13th of the month (since there are no months greater than 12).
That's why you need to put the Format statement in to convert it to an
unambiguous format. To be absolutely certain, you might use

Format(CDate(Form!NameOfForm!txtDateofBirth), "\#yyyy\-mm\-dd\#")), "0")

since CDate is one of the few functions that does respect regional settings.

For more information on this, see Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html
or what I had in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Guest]

Thanks for your time douglas as always.

I must admit that I actualy did get it to work but only with the postcode.
The daft thing is that is really does work fine on dates that are typed in -
ie old data before when went all "on-line". I think looking at it now that
the message I typed was not correct (the code is slightly different). But I
take your point about the "\#yyyy\-mm\-dd\#". I will look at it again on
Tuesday.

Thanks again for your interest - I always try (not always succesfully) to
answer 20 questions for each one I ask just to keep up wth the spirit of the
forum.

Mind you vba is simple when compared to explaining to my little girl why her
rabit has gone to sleep forever - - -

--
Wayne
Manchester, England.

I don't see how that can even compile, much less work, since your use of the
# delimiter is wrong. As well, Form!txtDateofBirth appears to be missing the
name of the form:

strID = Nz(DLookup("[CDClientID]", "[TblClients]", "[CD1stName] ='" & _
Form!txt1stName & "' AND [CDSurname] = '" & Form!txtSurname & _
"'AND [CDDateofBirth] = " & _
Format(Form!NameOfForm!txtDateofBirth, "\#yyyy\-mm\-dd\#")), "0")

Since you're in the UK, I suspect people are typing dates in dd/mm/yyyy
format. Just as in SQL statements, Access will not respect that format in
DLookup statements: it will always treat the date as mm/dd/yyyy unless it's
after the 13th of the month (since there are no months greater than 12).
That's why you need to put the Format statement in to convert it to an
unambiguous format. To be absolutely certain, you might use

Format(CDate(Form!NameOfForm!txtDateofBirth), "\#yyyy\-mm\-dd\#")), "0")

since CDate is one of the few functions that does respect regional settings.

For more information on this, see Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html
or what I had in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi all


TblClients
CD1stName = Text
CDSurname = Text
CDDateofBirth = Time/Date


The form is feb by a query and the date of birth used in the form is ...

CDDateOfBirth:
DateSerial(qryOnLineBookingsUKOP_QueryFeeder!Personal_DOBYear,qryOnLineBookingsUKOP_QueryFeeder!Personal_DOBMonth,qryOnLineBookingsUKOP_QueryFeeder!Personal_DOBDay)

Dim strID As String
strID = Nz(DLookup("[CDClientID]", "[TblClients]", "[CD1stName] ='"
& Form!txt1stName & "' AND [CDSurname] = '" & Form!txtSurname & "'AND
[CDDateofBirth] = '" #& Form!txtDateofBirth &# "'"), "0")

If strID = "0" Then
Etc
Etc


This "will" find records - one's that were type in on a key board. Also
if
the date of birth is taken out of the criteria it works fine - so I guess
the
problem is the date of birth and how it being constructed. (DateSerial).

I just need to find out if a record already exists and if it does ..do one
thing. If it doesn't .. do something else.

I can post the whole button code if need but it's quite long.

If anyone understands the above (is the syntax correct ?) and can give any
tips it would be "really" helpful (been looking this for hours).

Many thanks

 

[John W. Vinson]

This “will” find records – one’s that were type in on a key board. Also if
the date of birth is taken out of the criteria it works fine – so I guess the
problem is the date of birth and how it being constructed. (DateSerial).

That looks ok, if the year month and day are all numeric values. The problem
is that your delimiters are in the wrong place. Try

strID = Nz(DLookup("[CDClientID]", "[TblClients]", "[CD1stName] ='"
& Form!txt1stName & "' AND [CDSurname] = '" & Form!txtSurname & "'AND
[CDDateofBirth] = #" & Form!txtDateofBirth & "#"), "0")

John W. Vinson [MVP]

 

[Guest]

Thank you John

Your answers are always very clear and acurate. I will look at it in the
office on Tuesday.




--
Wayne
Manchester, England.

This “will†find records – one’s that were type in on a key board. Also if
the date of birth is taken out of the criteria it works fine – so I guess the
problem is the date of birth and how it being constructed. (DateSerial).

That looks ok, if the year month and day are all numeric values. The problem
is that your delimiters are in the wrong place. Try

strID = Nz(DLookup("[CDClientID]", "[TblClients]", "[CD1stName] ='"
& Form!txt1stName & "' AND [CDSurname] = '" & Form!txtSurname & "'AND
[CDDateofBirth] = #" & Form!txtDateofBirth & "#"), "0")

John W. Vinson [MVP]