Dlookup Problem Text VS Number type

[Zach]

Hello all-
I have a form (in access) that has a section of code which fills in certain
cells for an excel spread sheet. Code has worked perfectly for a while, but
recently I changed the table's 'OrderNumber' field from a Number type to a
Text type. Now my Dlookup doesn't work. I tried changing it back, and it
works again. Why does it matter? I need/would really like it to be a text
type field. All help/explinations would help a lot.

Thanks!
Zach

 

[Daryl S]

Zach -

Did you also update the DLookup statement? A text field requires the
single-quote delimiter around it, whereas a number field does not.

 

[XPS350]

Hello all-
I have a form (in access) that has a section of code which fills in certain
cells for an excel spread sheet.  Code has worked perfectly for a while, but
recently I changed the table's 'OrderNumber' field from a Number type to a
Text type.  Now my Dlookup doesn't work.  I tried changing it back, and it
works again.  Why does it matter?  I need/would really like it to be a text
type field.  All help/explinations would help a lot.

Thanks!
Zach

I suppose the WHERE-part makes the diffrence.

With a text field the Dlookup would look like:
Dlookup("FieldName","TableName","ID='A'")

With a number:
Dlookup("FieldName","TableName","ID=1")

Groeten,

Peter
http://access.xps350.com

 

[Jeanette Cunningham]

For a number you do something like this
"[PkID] = " & Me.PkID

For a text
"[PkID] = """ & Me.PkID & """"


Why does it matter?
It is crucial to use the correct quotes - delimiters - for numbers, text and
dates everywhere in an access database in code.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Zach]

Sorry I forgot to post a snipit of code.
DLookup("DUEDATE", "Orders", "[ordernumber]=" & [OrderNumber] & " And
[orderitem] = " & 1)

Daryl....
do you mean like this?
DLookup("DUEDATE", "Orders", '[ordernumber]=' & [OrderNumber] & " And
[orderitem] = " & 1)

Thanks for the response!

 

[Zach]

Hey Jeanette-
Thank you so much for your reply! I understand........makes
since.....already knew that for otherparts of my code.....but obviously
forgot about the concept as it applied to a Dlookup syntax. So again, thank
you for putting a end to my headache!

For a number you do something like this
"[PkID] = " & Me.PkID

For a text
"[PkID] = """ & Me.PkID & """"


Why does it matter?
It is crucial to use the correct quotes - delimiters - for numbers, text and
dates everywhere in an access database in code.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hello all-
I have a form (in access) that has a section of code which fills in
certain
cells for an excel spread sheet. Code has worked perfectly for a while,
but
recently I changed the table's 'OrderNumber' field from a Number type to a
Text type. Now my Dlookup doesn't work. I tried changing it back, and it
works again. Why does it matter? I need/would really like it to be a
text
type field. All help/explinations would help a lot.

Thanks!
Zach

.

 

[Daryl S]

Zach -

I think you have it, but just in case, this is it:

DLookup("DUEDATE", "Orders", "[ordernumber]='" & [OrderNumber] & "' And
[orderitem] = " & 1)

--
Daryl S

Sorry I forgot to post a snipit of code.
DLookup("DUEDATE", "Orders", "[ordernumber]=" & [OrderNumber] & " And
[orderitem] = " & 1)

Daryl....
do you mean like this?
DLookup("DUEDATE", "Orders", '[ordernumber]=' & [OrderNumber] & " And
[orderitem] = " & 1)

Thanks for the response!

Zach -

Did you also update the DLookup statement? A text field requires the
single-quote delimiter around it, whereas a number field does not.

 

[De Jager]

Zach -

I think you have it, but just in case, this is it:

DLookup("DUEDATE", "Orders", "[ordernumber]='" & [OrderNumber] & "' And
[orderitem] = " & 1)

--
Daryl S

Sorry I forgot to post a snipit of code.
DLookup("DUEDATE", "Orders", "[ordernumber]=" & [OrderNumber] & " And
[orderitem] = " & 1)

Daryl....
do you mean like this?
DLookup("DUEDATE", "Orders", '[ordernumber]=' & [OrderNumber] & " And
[orderitem] = " & 1)

Thanks for the response!

Zach -

Did you also update the DLookup statement? A text field requires the
single-quote delimiter around it, whereas a number field does not.

--
Daryl S


:

Hello all-
I have a form (in access) that has a section of code which fills in
certain
cells for an excel spread sheet. Code has worked perfectly for a
while, but
recently I changed the table's 'OrderNumber' field from a Number type
to a
Text type. Now my Dlookup doesn't work. I tried changing it back,
and it
works again. Why does it matter? I need/would really like it to be
a text
type field. All help/explinations would help a lot.

Thanks!
Zach