Problems formating an update query 1

[JamesBBB]

Hello

I have a little problem here which I just cant solve, if anyone can help, I would

be very grateful.

Basically within my database I have 3 columns, One is [Series No] (Number) and

the second is [Episode No](Number) and the last [Series Ref](Text).

What I want to do is update [Series Ref] with a fixed Format of SNNENN ie S01E01,

S01E02 etc.

However the numeric fields for [Episode No] and [Series No] default to single digits

ie 1 2 3 etc, even though I put a format on the field so that they show 01, 02

etc when viewing the table.

If I run an update query with the parameters
update to: "S" & [Series No] & "E" & [Episode No]

I get results like

S1E1
S2E2
S1E10 etc

rather than the format I want which is

S01E01
S02E02
S01E10 etc etc

I tried putting a criteria in such as

len([Episode Mo] = 1

and then if it = 1 I would pad out the above update to
update to: "S" & [Series No] & "E0" & [Episode No]

But I just cannot get it to work

Am I on the right track or approaching this problem, or am I looking at this from completely the wrong

angle

Any help would be gratefully received

Many thanks

James

 

[JBinQLD]

Formatting changes the view of data stored. It doesn't actually change the data stored. Don forget, when he rubber meets the road, computers are all 0's and 1's!

So, Access will STORE data in the most effecient way possible. The clue here is to format your data before saving as text:

Ie. Let access store 1 as Episode No and Series Ref as number, dont bother formatting (Really, you dont have a choice!)

and update to this in your query:

"S" & format([Series No],"00") & "E" & format([Episode No],"00")

Hope this helps, formatting is easily misunderstood!

JB

 

[JamesBBB]

Hi JBinQLD

Absolutely Fantastic, I cant thank you enough

many many thanks

James

 

[JBinQLD]

Too easy mate, no drama. It's always nice to help appreciative people. BUT - As I'm sure some other people will point out, there is no point in actually storing this value in your table as your page/form/whatever can do this same formatting for you before it presents it to the end user making for higher normalisation and hence more efficient storage.

Happy Dayz,

JB