DLookUp for an Alphanumeric Record

M

Midland

I am revisiting a problem with my data entry form, "MachInput" which with the
list helped me a few weeks ago.

I am populating the table "GMachine" with this form, using the Query "InQry"
as a calculating buffer so I can pull reference data on standard rates for
set-up and production.

Previously, I was pulling up rates from "InQry" with this expression:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
([pn]+[seq]/1000)))

[pn] is the part number, [seq] is a sub-process, and [QStRate] is a
caculated standard rate from “InQryâ€.

In the older version, each standard rate was designated by a composited ID
number based on the part number and sequence number, i.e. "51204801.010".
Thus, the PN and seq from the form could be used to reference the correct
rate.

For the new system, I need to use a Rate ID that incorporates the Work
Center number, i.e. “4810.51204801.010†or “8250CN.51204801.010â€. I can
create this alpha-numeric designator either within the form or within the
query, using a macro to update the fields while I am working on the record.

The expression used to build the rate ID in the query, using fields from the
data table, is this:

=[wc] & "." & Format([pn],"00000000") & "." & Format([seq],"000"

I therefore can link back to [RateID] in the query record to pull the rate,
or I can build it as a control in the form as [RateIDf]. That led me to
using this variation on the original expression to call up the standard rate:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
[MachInput]![RateIDf])

.. . . and I also tried this one:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" & ([wc] & "." &
Format([pn],"00000000") & "." & Format([seq],"000")

Neither of them gives me anything but an error message. When I simplify the
formula to try to read the ID directly, or otherwise experiment, I either get
a message telling me DLookUp cannot find the name, or it asks me to correct
the “number†with the two decimal points in it. It looks as though the
function does not work with an alpha-numeric ID number in a direct way. This
doesn’t seem logical for a data base function, but none of the definitions or
examples I have in my references tell me yea or nay on this point.

I could presumably some other kind of link to pull the ID, but I’m at a loss
as to what would work.
 
S

Steve Sanford

In the table, what data type is the field [RateID]?

If it is a text data type, then it needs to be delimited with single/double
quotes.


=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]='" &
[MachInput]![RateIDf] & "'")

or

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]='" & [wc] & "." &
Format([pn],"00000000") & "." & Format([seq],"000") & "'")
Click to expand...

Expanded, it looks like

"[RateID]= ' " & [MachInput]![RateIDf] & " ' ")

or

= ' " & [wc] & "." & Format([pn],"00000000") & "." & Format([seq],"000") & "
' ")


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Midland said:
I am revisiting a problem with my data entry form, "MachInput" which with the
list helped me a few weeks ago.

I am populating the table "GMachine" with this form, using the Query "InQry"
as a calculating buffer so I can pull reference data on standard rates for
set-up and production.

Previously, I was pulling up rates from "InQry" with this expression:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
([pn]+[seq]/1000)))

[pn] is the part number, [seq] is a sub-process, and [QStRate] is a
caculated standard rate from “InQryâ€.

In the older version, each standard rate was designated by a composited ID
number based on the part number and sequence number, i.e. "51204801.010".
Thus, the PN and seq from the form could be used to reference the correct
rate.

For the new system, I need to use a Rate ID that incorporates the Work
Center number, i.e. “4810.51204801.010†or “8250CN.51204801.010â€. I can
create this alpha-numeric designator either within the form or within the
query, using a macro to update the fields while I am working on the record.

The expression used to build the rate ID in the query, using fields from the
data table, is this:

=[wc] & "." & Format([pn],"00000000") & "." & Format([seq],"000"

I therefore can link back to [RateID] in the query record to pull the rate,
or I can build it as a control in the form as [RateIDf]. That led me to
using this variation on the original expression to call up the standard rate:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
[MachInput]![RateIDf])

. . . and I also tried this one:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" & ([wc] & "." &
Format([pn],"00000000") & "." & Format([seq],"000")

Neither of them gives me anything but an error message. When I simplify the
formula to try to read the ID directly, or otherwise experiment, I either get
a message telling me DLookUp cannot find the name, or it asks me to correct
the “number†with the two decimal points in it. It looks as though the
function does not work with an alpha-numeric ID number in a direct way. This
doesn’t seem logical for a data base function, but none of the definitions or
examples I have in my references tell me yea or nay on this point.

I could presumably some other kind of link to pull the ID, but I’m at a loss
as to what would work.
Click to expand...
 
M

Midland

Useful hint! I tracked back through the source and determined that the ID
was text back in the original Datalink table.

It followed, then, that I didn't have to do anything complicated. The
expression:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=[RateIDf]")

.. . . did the job.

Remaining issue: the standard rate is a general number along the lines of .
.. . 18.7345453 . . . and similar. I need to format it so only . . . 18.73 . .
.. appears in the control. However, it is ignoring all formating instructions
I enter: fixed, general, auto decimal places, 2 decimal places. Why would it
do that?
--
Midland


Steve Sanford said:
In the table, what data type is the field [RateID]?

If it is a text data type, then it needs to be delimited with single/double
quotes.


=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]='" &
[MachInput]![RateIDf] & "'")

or

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]='" & [wc] & "." &
Format([pn],"00000000") & "." & Format([seq],"000") & "'")
Click to expand...

Expanded, it looks like

"[RateID]= ' " & [MachInput]![RateIDf] & " ' ")

or

= ' " & [wc] & "." & Format([pn],"00000000") & "." & Format([seq],"000") & "
' ")


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Midland said:
I am revisiting a problem with my data entry form, "MachInput" which with the
list helped me a few weeks ago.

I am populating the table "GMachine" with this form, using the Query "InQry"
as a calculating buffer so I can pull reference data on standard rates for
set-up and production.

Previously, I was pulling up rates from "InQry" with this expression:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
([pn]+[seq]/1000)))

[pn] is the part number, [seq] is a sub-process, and [QStRate] is a
caculated standard rate from “InQryâ€.

In the older version, each standard rate was designated by a composited ID
number based on the part number and sequence number, i.e. "51204801.010".
Thus, the PN and seq from the form could be used to reference the correct
rate.

For the new system, I need to use a Rate ID that incorporates the Work
Center number, i.e. “4810.51204801.010†or “8250CN.51204801.010â€. I can
create this alpha-numeric designator either within the form or within the
query, using a macro to update the fields while I am working on the record.

The expression used to build the rate ID in the query, using fields from the
data table, is this:

=[wc] & "." & Format([pn],"00000000") & "." & Format([seq],"000"

I therefore can link back to [RateID] in the query record to pull the rate,
or I can build it as a control in the form as [RateIDf]. That led me to
using this variation on the original expression to call up the standard rate:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
[MachInput]![RateIDf])

. . . and I also tried this one:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" & ([wc] & "." &
Format([pn],"00000000") & "." & Format([seq],"000")

Neither of them gives me anything but an error message. When I simplify the
formula to try to read the ID directly, or otherwise experiment, I either get
a message telling me DLookUp cannot find the name, or it asks me to correct
the “number†with the two decimal points in it. It looks as though the
function does not work with an alpha-numeric ID number in a direct way. This
doesn’t seem logical for a data base function, but none of the definitions or
examples I have in my references tell me yea or nay on this point.

I could presumably some other kind of link to pull the ID, but I’m at a loss
as to what would work.
Click to expand...
Click to expand...
 
S

Steve Sanford

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=[RateIDf]")
Click to expand...

You need to concatenate the value in the control [RateIDF]. It should be:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID] = " & [RateIDf])


How is it ignoring all formating instructions???
In the table, what is the datatype?
If the value in the table is 18.7345453, what is the value displayed in the
control??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Midland said:
Useful hint! I tracked back through the source and determined that the ID
was text back in the original Datalink table.

It followed, then, that I didn't have to do anything complicated. The
expression:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=[RateIDf]")

. . . did the job.

Remaining issue: the standard rate is a general number along the lines of .
. . 18.7345453 . . . and similar. I need to format it so only . . . 18.73 . .
. appears in the control. However, it is ignoring all formating instructions
I enter: fixed, general, auto decimal places, 2 decimal places. Why would it
do that?
--
Midland


Steve Sanford said:
In the table, what data type is the field [RateID]?

If it is a text data type, then it needs to be delimited with single/double
quotes.


=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]='" &
[MachInput]![RateIDf] & "'")

or

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]='" & [wc] & "." &
Format([pn],"00000000") & "." & Format([seq],"000") & "'")
Click to expand...

Expanded, it looks like

"[RateID]= ' " & [MachInput]![RateIDf] & " ' ")

or

= ' " & [wc] & "." & Format([pn],"00000000") & "." & Format([seq],"000") & "
' ")


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Midland said:
I am revisiting a problem with my data entry form, "MachInput" which with the
list helped me a few weeks ago.

I am populating the table "GMachine" with this form, using the Query "InQry"
as a calculating buffer so I can pull reference data on standard rates for
set-up and production.

Previously, I was pulling up rates from "InQry" with this expression:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
([pn]+[seq]/1000)))

[pn] is the part number, [seq] is a sub-process, and [QStRate] is a
caculated standard rate from “InQryâ€.

In the older version, each standard rate was designated by a composited ID
number based on the part number and sequence number, i.e. "51204801.010".
Thus, the PN and seq from the form could be used to reference the correct
rate.

For the new system, I need to use a Rate ID that incorporates the Work
Center number, i.e. “4810.51204801.010†or “8250CN.51204801.010â€. I can
create this alpha-numeric designator either within the form or within the
query, using a macro to update the fields while I am working on the record.

The expression used to build the rate ID in the query, using fields from the
data table, is this:

=[wc] & "." & Format([pn],"00000000") & "." & Format([seq],"000"

I therefore can link back to [RateID] in the query record to pull the rate,
or I can build it as a control in the form as [RateIDf]. That led me to
using this variation on the original expression to call up the standard rate:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" &
[MachInput]![RateIDf])

. . . and I also tried this one:

=IIf([pn]=0,"--",DLookUp("[QStRate]","[InQry]","[RateID]=" & ([wc] & "." &
Format([pn],"00000000") & "." & Format([seq],"000")

Neither of them gives me anything but an error message. When I simplify the
formula to try to read the ID directly, or otherwise experiment, I either get
a message telling me DLookUp cannot find the name, or it asks me to correct
the “number†with the two decimal points in it. It looks as though the
function does not work with an alpha-numeric ID number in a direct way. This
doesn’t seem logical for a data base function, but none of the definitions or
examples I have in my references tell me yea or nay on this point.

I could presumably some other kind of link to pull the ID, but I’m at a loss
as to what would work.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formating Issue in a Query 1
Invalid syntax in dlookup 4
Concatenate Serial # and Part # to create value for key field 3
Dlookup and simple calculation 2
DLookUp #Error 7
DLookup-retrieves only 1st record in query 3
Dlookup Where Clause 2
Dlookup() 7

Top