1

This may be a basic question but I have no idea on how to word it in Google.

Basically, I have an Access database with Primary keys that are structured with this format ("02"000).

When I try to paste the number, it ignores the "02" and goes straight to 000.

For example, here is the code;

PeriodRoomID.Name = ("R" & ds.Tables("sqlSpecRoomRequest").Rows(i).Item(1))

What PeriodRoomID.Name should be is R02001. What PeriodRoomID.Name comes up as is R1.

It ignores the "02" and ignores the 0s. This makes sense mathematically but it is not what I want. I need a way to get the exact output and not some simplified version.

The query;

SELECT SpecialSoftware.SpecSoftID, SpecialSoftware.RoomID, SpecialSoftwareNames.Description, Rooms.Capacity
FROM SpecialSoftware, SpecialSoftwareNames, Rooms
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Pejman Poh
  • 493
  • 2
  • 8
  • 20
  • What is the type of the column, and what does your query look like? – Jon Skeet Feb 06 '12 at 06:23
  • You mean the data type? Its a Number. I'll post the query above. – Pejman Poh Feb 06 '12 at 07:42
  • If it's a number, how can it have quotes in? That looks very odd to me. – Jon Skeet Feb 06 '12 at 08:24
  • The way Access format works is like this. "02" means that the record MUST have the number 02 in the beginning. The 000 after that indicates that 3 INTEGERS must be present. If not, then it throws an error. Thats what the format is for. – Pejman Poh Feb 06 '12 at 08:34
  • 1
    @user1181824 That is not even close to what format does in MS Access. Format is purely display, not requirement. – Fionnuala Feb 06 '12 at 14:29
  • @Remou So what are you are saying is that format is only advice and it does not have to be followed? Or it does not have any effect on the code? – Pejman Poh Feb 07 '12 at 05:56
  • Format does not have any effect on code. It merely shows data in tables and forms in a certain way. I do not believe formats of this type should ever be used in tables because it leads to this kind of confusion. – Fionnuala Feb 07 '12 at 10:11

3 Answers3

1

I suggest that you have an autonumber field with a format set to "02"000. All the format does is display the data to an Access user in that format. The field does not contain 02001, it contains 1. If you wish to recreate the format, you will have to do so in your application. Updating the autonumber to n will show as n in ADO and ODBC, but as 0200n in various places in Access - a format is not even relevant to queries in Access. I do not believe a format in a table is a good idea.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • If you re-read my answer, you will notice that it points out information relevant to your query, that is, format is not relevant to your query. – Fionnuala Feb 07 '12 at 10:09
1

You could just format the number inside your app:

Dim roomID As String = ds.Tables("sqlSpecRoomRequest").Rows(i).Item(1)
PeriodRoomID.Name = "R02" & roomID.PadLeft(3, "0"c)
Meta-Knight
  • 17,626
  • 1
  • 48
  • 58
  • Hey that worked :) Although could you explain what PadLeft does, the Microsoft explanation didn't make much sense and Google doesn't either. – Pejman Poh Feb 07 '12 at 06:23
  • PadLeft(3,"0"c) makes the string 3 characters long, by adding zeroes (character 0) to the left if necessary. – Meta-Knight Feb 07 '12 at 13:46
0

Can you access to the "02" only? You wrote your query ignores "02", so it looks you need to have a single query for this piece of information.
I'm no expert in vb or access, but it looks like you can use a quick'n'dirty solution: You wrote, your keys have the form "02"000. I would take out each number by itself, check the number of digits, convert it to string and fill all leading zeros that are missing, because you know the maximum number of digits.

Referring to what overall comments show: Filling dynamically leading zeros and referring to your table by name to decide by if-clause if your room-ID starts with R01 or R02.

Shegit Brahm
  • 725
  • 2
  • 9
  • 22
  • How would I take out each number by itself if I can't access all the numbers from vb? – Pejman Poh Feb 06 '12 at 08:35
  • Question: do you have two different columns containing your two numbers? Where does the 02 comes from? Regarding to your original post it sounds that there should be more than 02. So my question is, where did you determine that. If every room would have this 02, than don't worry and write it manually. – Shegit Brahm Feb 06 '12 at 08:51
  • No it is under one column. The basic idea is that I have 4 tables, each of which has its own primary key. First table has all the primary keys starting with 01, second table with 02, third with 03, fourth with 04. That way I can distinguish between the primary keys. Rooms is the second table and therefore all rooms start with 02. The autonumber only changes the last 3 numbers. Therefore when I say "02"000, these last 3 000 are autonumber but they all start with 02. – Pejman Poh Feb 06 '12 at 13:02
  • So you have four different tables, and each table has a 3-digit-primary-key starting with 000? So how do you tell your query which table it has to look up? Because it looks that you could get your "02" out of this. You said a) you receive only 1 instead of 02001, b) you have different tables [named with 01, 02?] and c) each table has its own primary keys. So I would say that a lookup for primary key can't give you key of your table. You understand? Or did you merge all four tables into one? – Shegit Brahm Feb 06 '12 at 13:28
  • The tables are not named with 01, 02... they are proper words. The primary keys are unique because they start with 01, 02, 03 and 04. – Pejman Poh Feb 07 '12 at 06:27