Two fields in Query

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

Text(field) Numeric(field) New field (text
00139 139 00139
01745 1745 01745

In my query I want to add a new field to join
the two fields as one. I want my numbers to look
like the text field. i.e 00139, 01745. What kind
of formula can I use to create a new field like the above?
 
Allison

You may not need to add a field to your query.

If you already have a text field holding "00139", why do you want a new
field?

If you want your numeric field to LOOK like the text field, consider
changing the format -- you could change the format property to have
leading/padding zeros displayed, even though the number would still be
stored as a number.

Why do you have both text and numeric fields with the same "value"?
 
You could try something like

SELECT tblInvoice2.InvNo
FROM tblInvoice INNER JOIN tblInvoice2 ON tblInvoice.InvNo = right$("00000"
& tblInvoice2.InvNo,5);

HTH
 
Jeff,


I have two different linked tables on a daily basis I have
reconcile each system based on the Grant field. In one
linked table the field name is Grant# in the other table
the field is called Serial #. Keep in mind the two are the
same). I'm generally performing an unmatched query. I make
a copy of both make table queries and go into change the
data type from number to text in one of the tables in
order to perform any type of unmatched query but the next
problem I have the numbers are not formatted the same. 139
vs 00139 or 1745 vs. 01745, a serial or grant number
consist of 5 i.e. 67896
 
Back
Top