DLookup question

[ryan.fitzpatrick3]

I have this code

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.

Ryan

 

[mscertified]

use = RIGHT$("00000" & YourValue,5)

-Dorian

 

[Klatuu]

What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")

 

[ryan.fitzpatrick3]

crop_item_cd is a numeric data type. so with your formula below would
I do this

=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)

or do what the gentleman above wrote

=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP

I have this code

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.

Ryan

 

[Klatuu]

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP

crop_item_cd is a numeric data type. so with your formula below would
I do this

=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)

or do what the gentleman above wrote

=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP

I have this code

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.

Ryan

 

[ryan.fitzpatrick3]

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP

crop_item_cd is a numeric data type. so with your formula below would
I do this

=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)

or do what the gentleman above wrote

=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP
:
I have this code
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.
Ryan

 

[Klatuu]

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)

That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD

--
Dave Hargis, Microsoft Access MVP

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP

crop_item_cd is a numeric data type. so with your formula below would
I do this

=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)

or do what the gentleman above wrote

=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")

:
I have this code

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.

Ryan

 

[ryan.fitzpatrick3]

I posted your code into the text box. I deleted the me. because it's
not vba, i forgot to disclose that. I did expression builder in the
unbound text box. so I put

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(tblItemNumber)),0)

It still came up with just 3, instead of 00003, should I put " " in
there to convert to text?

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)

That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD

--
Dave Hargis, Microsoft Access MVP

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP
:
crop_item_cd is a numeric data type. so with your formula below would
I do this
=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)
or do what the gentleman above wrote
=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP
:
I have this code
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.
Ryan

 

[ryan.fitzpatrick3]

To be clear I want the zero's added and not stripped of them. so any
number would look like this i.e.

1 = 00001
123 = 00123
1234 = 01234
12345 = 12345

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)

That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD

--
Dave Hargis, Microsoft Access MVP

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP
:
crop_item_cd is a numeric data type. so with your formula below would
I do this
=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)
or do what the gentleman above wrote
=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP
:
I have this code
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.
Ryan

 

[ryan.fitzpatrick3]

Another question for you. I know dlookup only gets the first record in
the database. I have some item numbers that have multiple UPC's is
there away to show a list of the UPC's? I was thinking of something
like this.

tbox1 (unbound) the first UPC would go in here,
tbox2 (unbound) the second UPC would go in here, if no 2nd UPC then I
could disable the tbox to not be enabled
tbox3 (unbound) etc
tbox4 (unbound) etc

or should I use a list box to show them all in one list box?

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)

That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD

--
Dave Hargis, Microsoft Access MVP

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP
:
crop_item_cd is a numeric data type. so with your formula below would
I do this
=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)
or do what the gentleman above wrote
=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP
:
I have this code
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.
Ryan

 

[Klatuu]

Okay, I see what you are doing. In this case, Me. should not be used. Also,
you first have to unformat the value so the DLookup can match it, then you
want the control to format it.
In the properties dialog for the text box that has that the expression is
in, go to the Format tab and in the format property put 00000
Then it will show as 00003
--
Dave Hargis, Microsoft Access MVP

I posted your code into the text box. I deleted the me. because it's
not vba, i forgot to disclose that. I did expression builder in the
unbound text box. so I put

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(tblItemNumber)),0)

It still came up with just 3, instead of 00003, should I put " " in
there to convert to text?

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)

That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD

--
Dave Hargis, Microsoft Access MVP

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.

:
crop_item_cd is a numeric data type. so with your formula below would
I do this

=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)

or do what the gentleman above wrote

=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")

:
I have this code

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.

Ryan

 

[ryan.fitzpatrick3]

Did you receive my last posts, I posted 3 messages but it hasn't
popped up yet upon refresh.

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)

That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD

--
Dave Hargis, Microsoft Access MVP

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP
:
crop_item_cd is a numeric data type. so with your formula below would
I do this
=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)
or do what the gentleman above wrote
=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP
:
I have this code
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.
Ryan

 

[ryan.fitzpatrick3]

t

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.

crop_item_cd is a numeric data type. so with your formula below would
I do this
=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)
or do what the gentleman above wrote
=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP
:
I have this code
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.
Ryan

 

[ryan.fitzpatrick3]

Im unsure if you got this, google is refreshing slow.

Another question for you. I know dlookup only gets the first record in
the database. I have some item numbers that have multiple UPC's is
there away to show a list of the UPC's? I was thinking of something
like this.

tbox1 (unbound) the first UPC would go in here,
tbox2 (unbound) the second UPC would go in here, if no 2nd UPC then I
could disable the tbox to not be enabled
tbox3 (unbound) etc
tbox4 (unbound) etc

or should I use a list box to show them all in one list box?

Okay, I see what you are doing. In this case, Me. should not be used. Also,
you first have to unformat the value so the DLookup can match it, then you
want the control to format it.
In the properties dialog for the text box that has that the expression is
in, go to the Format tab and in the format property put 00000
Then it will show as 00003
--
Dave Hargis, Microsoft Access MVP

I posted your code into the text box. I deleted the me. because it's
not vba, i forgot to disclose that. I did expression builder in the
unbound text box. so I put

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(tblItemNumber)),0)

It still came up with just 3, instead of 00003, should I put " " in
there to convert to text?

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)
That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD
--
Dave Hargis, Microsoft Access MVP
:
UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
is the textbox that populates the UPC_SALES number.
No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.
--
Dave Hargis, Microsoft Access MVP
:
crop_item_cd is a numeric data type. so with your formula below would
I do this
=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)
or do what the gentleman above wrote
=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")
--
Dave Hargis, Microsoft Access MVP
:
I have this code
=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)
this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.
Ryan

 

[Klatuu]

Sorry for the delayed response. I was off for a couple of days this week.
All your posts got throuhg. A refresh wont do it. It takes about 5 minutes
for a post to show up.

Since you want multiples, a list box would do. You could create a query for
the row source of the list box that would return the records you want.

Again about the numbers. Numbers are not stored with leading zeros unless
it is a text box where you store it that way, but that would not be a wise
choice. Computers don't care about leading zeros. Only we humans want to
see it that way. That is one of the uses of the format function, to present
a value in the way we want to see it.
--
Dave Hargis, Microsoft Access MVP

Did you receive my last posts, I posted 3 messages but it hasn't
popped up yet upon refresh.

It is always best to qualify your objects. Since CORP_ITEM_CD is a numer
field, you need to look it up with a numeric value. Here is what I would
recommend:

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
CLng(Me.tblItemNumber)),0)

That strips the leading zeros off and converts it to a Long data type to
match CORP_ITEM_CD

--
Dave Hargis, Microsoft Access MVP

UPC_SALES is the UPC number field
SQLDAT3_SSITMXRF is the database table
CORP_ITEM_CD is the item number that links to the UPC_SALES
tblItemNumber is the text box where I input the item number.

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

is the textbox that populates the UPC_SALES number.

No, you wouldn't do either or those.
What is [tblItemNumber]?
Is it a control on your form, or what?
Let me know and I can show you the correct syntax.

:
crop_item_cd is a numeric data type. so with your formula below would
I do this

=nz(DLookUp(Format("[UPC_SALES]","00000"),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD]
= " &
[tblItemNumber]),0)

or do what the gentleman above wrote

=nz(DLookUp(RIGHT$("00000" & "[UPC_SALES]",
5),"SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

What data type is [CORP_ITEM_CD]?
As written, you code is expecting a numeric data type. Therefore, there
would be no leading zeros. Numbers don't have leading zeros. You can
present them to the user that way using the Format function. For example to
get a string 5 characters long with leading zeros, it would be:
=Format(127,"00000")

:
I have this code

=nz(DLookUp("[UPC_SALES]","SQLDAT3_SSITMXRF","[CORP_ITEM_CD] = " &
[tblItemNumber]),0)

this works properly but i need to be in number format. right now this
pulls the entry "3", but can it be in this format "00003". Also if i
pull another record that is "127" could it show as "00127", I need 0's
to fill the slots where the number doesn't, make sense. Thanks.

Ryan