473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Copying Autonumber Formats in Query

121 100+
I have a table called "Expenses" with an autonumber field "Expense ID". In the "Format" property of the "Expense ID" i have set it up as "EXP"000#. So my numbers look lke this: EXP0001, EXP0002, EXP0003.

Now, I have a query that updates that "Expense ID" number to another table. But when I run the query, all I get is the number, not the format text with it.

So in the new table my numbers look like: 1, 2, 3.


Is there a way to get Access to copy the numbers with the text format?


Thanks in advance for any help
Mar 6 '07 #1
7 1895
Rabbit
12,516 Expert Mod 8TB
Format only tells Access what to show to the viewer and does not store the format unless you tell it to. I forget the exact syntax and I have a meeting to get to, i'll get back to you once I'm out of the meeting unless someone else does it first.
Mar 6 '07 #2
JHNielson
121 100+
Format only tells Access what to show to the viewer and does not store the format unless you tell it to. I forget the exact syntax and I have a meeting to get to, i'll get back to you once I'm out of the meeting unless someone else does it first.

Great - thanks. I'll look forward to your help.
Mar 6 '07 #3
Rabbit
12,516 Expert Mod 8TB
"Input Mask";0;" "

Part 1 is the input mask.

Part 2 is 0 to store the mask along with what is typed and 1 is to only store what is typed.

Part 3 is how to display blanks.
Mar 6 '07 #4
JHNielson
121 100+
"Input Mask";0;" "

Part 1 is the input mask.

Part 2 is 0 to store the mask along with what is typed and 1 is to only store what is typed.

Part 3 is how to display blanks.

Thanks! Where would I put that? I'm assuming in the number format area.. is that right?

Thanks again....
Mar 6 '07 #5
Rabbit
12,516 Expert Mod 8TB
You'd put it in the input mask property of the field or control, depending on where you originally had that EXPxxxx input mask.
Mar 6 '07 #6
JHNielson
121 100+
"Input Mask";0;" "

Part 1 is the input mask.

Part 2 is 0 to store the mask along with what is typed and 1 is to only store what is typed.

Part 3 is how to display blanks.

I put it in this way: "INDV"0000;0;"".

In my source table it looks right. all the numbers look like: INDV0001, INDV0002, etc.

But when I run the query to update the other table with those codes, I just get 1, 2, 3, etc.

That's the problem I'm trying to solve. how do I get it to copy the whole value: "INDV0001"?


Thanks
Mar 6 '07 #7
Rabbit
12,516 Expert Mod 8TB
The problem is that the input mask doesn't go into effect until after it is put in place so it doesn't affect the older records.

You'll need to create a calculated field:
Expand|Select|Wrap|Line Numbers
  1. Expr1: Left("INDV000", 8 - Length([Field Name])) & [Field Name]
Mar 6 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: John | last post by:
Hi I have linked a master and a detail table in a 'make-table' query. Both tables have their own autonumber id fields. I am getting the 'Resultant table not allowed to have more than one...
5
by: jonhanks | last post by:
I'm trying to copy a table and its structure from DBase into Access. One of the fields is a primary key and contains numbers from 0 to 20000 and then from 30000 to 40000. How can I make this...
4
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query,...
5
by: marko | last post by:
I would like my autonumber field to start at 100000. How can i do that?
8
by: daD | last post by:
I'm trying to write a small database that tracks people coming and going from a small campground. I need to have the current guests in the "current" table" and then have the ability to check them...
11
by: fyshfysh | last post by:
Hi all, A quick question about a function which surely should be possible in a Make-Table Query in Access. I wonder if it's possible to very simply have a field that numbers the rows in the...
6
by: PeteCresswell | last post by:
Got a table with "RecordID"=AutoNumber=PrimaryKey. Call it "tblIssuerRating". Ran a query that appends to that table from a work table. Inadvertantly appended from the work table's...
6
by: Wayne | last post by:
I'm using the following SQL statement to find the next highest autonumber value in a table where "CDUGActID is the autonumber field in the "CDUGActuals" table: SELECT CDUGActuals.CDUGActID,...
6
by: ashes | last post by:
Hi, I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net When someone wants to register on the website, they fill out a form and the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.