INSERT query and strange parameter prompting

[omnialive]

Firstly, I have minimal previous experience with Access but year of experience with SQL, databases and the like (former Informix DBA and Unix Sys Admin).

I have an INSERT query that I use to grab data from a table and concatenate it together to create a specific string and store that string onto a temp table for later processing.

Here is an example table and INSERT query to illustrate what is happening.

Table [PERSON] has three columns: LAST_NAME, FIRST_NAME and SSN.

Sample data on table is:

LAST_NAME = KEITEL
FIRST_NAME = HARVEY
SSN = 999-00-1234

What the INSERT statement looks like is this:

INSERT INTO [TEMPTABLE] ([STRING], [DATE])
SELECT 'DISPLAY NAME: ' & [PERSON].[LAST_NAME] & ', ' & [PERSON].[FIRST_NAME] & CHR(12) &
'DISPLAY SSN: ' & [PERSON].[SSN] & CHR(12) &
'AUTHOR: ' & [PERSON].[LAST_NAME] & ', ' & [PERSON].[FIRST_NAME] & CHR(12) &
'AUTHOR SSN: ' & [PERSON].[SSN] & CHR(12) AS [STRING],
NOW() AS [DATE]
FROM PERSON

What is happening is that I am getting prompted to enter in a value for the second concatenated instances of SSN and FIRST_NAME. However, LAST_NAME doesn't generate a prompt and works just fine.

Any ideas on why I am being prompted for SSN and FIRST_NAME? I understand that this example is rather silly, but it illustrates exactly what I am struggling with.

I've checked the simple things like spelling in my query and et cetera and the invocations of concatenating the columns are spelled exactly the same! I have been pulling my hair out trying to figure this out and searching all over the internet (which is how I found this group). Any help or guidance is greatly appreciate!!!!!!!!!!!

 

[John Big Booty]

Firstly welcome to the forum.

I've copied and pasted your SQL as presented straight into my Sand Box DB and have been unable to reproduce the behaviour you report, in fact it seems to work fine are you able to post a copy of your DB?

 

[Galaxiom]

Sometimes, particularly after editing fieldnames, the query can get corrupted. Even though the sql and design view is legitimate the internal representation is corrupted.

Copy and paste the sql into a new query.

 

[omnialive]

It is good to be here! Thanks!

I'm leaning towards something being corrupted myself! I was handed this Access nightmare and am trying to clean it up. I did already copy and paste the query into a new query but that didn't fix the problem.

I am using Access 2007, but I believe this DB was created with a previous version of Access because at one point I was asked about converting the database to the latest version.

Wish I could post my DB on here, but it is proprietary. Don't think work would like me to do that! I am able to host web conferences if someone would be interested in doing that and you could tinker with it via that mechanism. That is the only option I have available unfortunately.

If you are interested in that let me know and we can PM to figure out the details. I will keep trying things on my end as well. Cheers!

 

[Galaxiom]

Maybe the problem is the original table, particularly if yo have changed table or fieldnames.

Objects created in older versions without Autocorrect can get corrupted when imported into versions with Autocorrect running (hence it sometimes being known as Autocorrupt).

Though it would seem weird it only prompts for the second instance. I assume you discoved this by what is inserted.

Incidentally you don't need to repeat the fieldnames in the SELECT. They are assumed to be in the same order as the INSERT specifies.

Seriously consider changing the name of both Date and String as they are reserved words and could cause trouble sometime down the track.

 

[omnialive]

The problem turned out to be some form of corruption. I know that string and date are reserved words. This was just for an example as I couldn't actually post my original database up here.

Thanks for all the replies!

 

[boblarson]

Oh, and why the CHR(12)?? If you want a new line it really should be Chr(13) & Chr(10) and not a page break.

 

[omnialive]

Oh, and why the CHR(12)?? If you want a new line it really should be Chr(13) & Chr(10) and not a page break.

Speaking to this it was because the file I am ultimately generating is an HL7 file and that was my segment delimiter I was using because CHR(12) is the *nix newline and was going to make it more readable on the AIX box.

 

[Galaxiom]

I was using because CHR(12) is the *nix newline.

Chr(12) is the Form Feed (FF) , New Page (NP) in both *nix and Windows.

The Unix New Line (NL), Line Feed (LF) is Chr(10)

Are you sure you have not confused the Octal representation of LF which is 012 ?

 

[omnialive]

Chr(12) is the Form Feed (FF) , New Page (NP) in both *nix and Windows.

The Unix New Line (NL), Line Feed (LF) is Chr(10)

Are you sure you have not confused the Octal representation of LF which is 012 ?

I did get the decimal and octal representations mixed up. Some proprietary programming languages I use refer to octal and then some refer to decimal.