Insert query and Leading 0's in field names

[Timtropolis]

Append query and Leading 0's in field names

Greetings all

Came across a problem and wanted to see if anyone else has seen this or knows the cause.

I am working in Access 2003.

I have two tables stored in a backend. One table updates the other. The database I inherited was doing this via the copyobject. I wanted to use a del query to delete recs in target table then an append query to update from source table to target table.

Both tables have data fields named in the following format: 01/2007, 02/2007, etc.
If I write a select query and save it, it works fine. However, if I write an Append query, the INSERT clause chops off the leading 0 of all fields upon save which causes the query to not work correctly. Anyone know why this happens?


TIA
Tim

 

[RuralGuy]

Try enclosing your field names in brackets []! [01/2007] [02/2007]
Special characters that you must avoid when you work with Access databases

 

[Moniker]

Also, when working with dates, the formatting is a little different:

INSERT INTO yourtablename (datefieldname, someotherfield)
SELECT #01/2007# AS datefieldname, "something else to update" AS someotherfield;

The pound sign (#) around the date takes that format. You can also just use a format function there (SELECT Format(#01/2007#,"mm/yyyy")) to force formatting.

 

[Timtropolis]

Hey Guys,

First, ty for the replies.

Ruralguy:

all fields are enclosed in []'s. I know the that the forward slash should be avoided at all costs but I've been told to name the fields this way so I'm stuck with having to oblige the boss in this case.

Moniker:
I should explain maybe that although the fields are named in a date format, the data contained within them are numerics (Long type).

I will take your advice tho with regards to the format and see if that works.
Just wierd that the leading 0 in the SELECT clause is not effected, but the leading 0 in the INSERT clause is.

 

[boblarson]

Hey Guys,

First, ty for the replies.

Ruralguy:

all fields are enclosed in []'s. I know the that the forward slash should be avoided at all costs but I've been told to name the fields this way so I'm stuck with having to oblige the boss in this case.
.

I've had that happen before but you gotta be the expert here and tell them that you can either have the fields named that way or have it work, your choice.

 

[RuralGuy]

Any chance you can post the SQL for the Append query so we can have a look/see?

 

[Timtropolis]

Update

Well after fooling with the query and not getting anywhere I decided to remove the link to this table and re-link. Guess what? Query saved and did
not chop off the leading 0's. I'm not even going to try and explain it cause it makes no sense, but I wanted to let you guys know that I was able to resolve it, even if I don't understand it.

Thanks again!

 

[RuralGuy]

Glad you got it working. I hope the problem doesn't decide to come back.

 

[neileg]

I know the that the forward slash should be avoided at all costs but I've been told to name the fields this way so I'm stuck with having to oblige the boss in this case.

Since the users should never see the field names, why does it matter? If your boss is so knowledgable about databases, let him write it.