Format advice does not work

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

Allison

I have an unmatched query using two different types:

Table # 1 has a text data type and the
serial number appears ex. 139.


Table # 2 has a text data type and the serial
number appears ex. 00139.


I want the tables to remain text I also want my field to
look like this 00139 instead of 139, but when I try and
perform an unmatched query on this field my results
are incorrect.


Your group advised using the following:

Val() function or Format([NameofYourField]),"00000")

My question is where will I place the format in the table
design, in the query design or in my report and how is it
done?
 
Hi Allison,

Try using the LPad function within the article
http://support.microsoft.com/default.aspx?scid=KB;EN-US;210573

You might have to use a temporary table though. Example:

- Use the LPad function in a MakeTable query to create your temporary table
- Use your temporary table to perform your UnMatch Query

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| Content-Class: urn:content-classes:message
| From: "Allison" <[email protected]>
| Sender: "Allison" <[email protected]>
| Subject: Format advice does not work
| Date: Tue, 6 Jul 2004 10:54:06 -0700
| Lines: 25
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| thread-index: AcRjgjsWOG7ypd4IQG6SrC2z4wbQUw==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:205925
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have an unmatched query using two different types:
|
| Table # 1 has a text data type and the
| serial number appears ex. 139.
|
|
| Table # 2 has a text data type and the serial
| number appears ex. 00139.
|
|
| I want the tables to remain text I also want my field to
| look like this 00139 instead of 139, but when I try and
| perform an unmatched query on this field my results
| are incorrect.
|
|
| Your group advised using the following:
|
| Val() function or Format([NameofYourField]),"00000")
|
| My question is where will I place the format in the table
| design, in the query design or in my report and how is it
| done?
|
|
|
 
Back
Top