SQL Update problem

[pmcgreevy]

Please excuse a VFP developer trying to work in MS Access.

I have a database which I created in Access 2007 and then retro'd to Access 2003.

In this database is a table called Ajh82Work which has a one character text field called CR.

I am trying to Update this field by inserting a carriage return into it. This is needed so a Unix server will translate the file from a flat ASCII file properly.

I tried the Update queries listed in the snippet below. I do not get an error, but the field doesn't update either.

I then opened the table, and used <CTRL>+<Enter> to try to input the carriage return manually. All I got was beeped at.

I then opened the table in design view and set the default value for that field first to chr(13) and then to chr$(13).

This doesn't affect the data records I am importing, but the last record, for adding an additional record, has #Error in the CR field, not a carriage return.

This field will not accept a text char either, while the other text fields in the table will.

I can <CTRL>+<;> to enter today's date in a date field, but this darn CR field just won't take a chr() value.

All the other fields I update work fine.

Anyone have any suggestions?

Thanks in advance for any help offered.

Pat

Dim strSQL As String
strSQL = "Update [ajh82Work] Set [Ajh82Work].[CR] = Chr(13)"
'strSQL = "Update ajh82Work Set [Ajh82Work].[CR] = Chr$(13)"
'strSQL = "Update ajh82Work Set [Ajh82Work].[CR] = vbCR"
CurrentDb.Execute strSQL
DoEvents

 

[genomon]

a one character text field called CR....

this darn CR field just won't take a chr() value

That is because the graphical representation chr(13) is seven characters long.

Cogito eggo sum – I think, therefore I am a waffle.

 

[pmcgreevy]

Thanks genomon, but increasing the width of the field to 7 chars didn't help. Still won't accept input to that field.
Pat

 

[genomon]

If you are updating with SQL, you need to assign a primary key to the table. Highlight the fields you want in design view, and click on the key icon in the menu bar.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

I just created a table and field with your names and successfully ran this in the immediate window:

Currentdb.Execute "Update [ajh82Work] Set [Ajh82Work].[CR] = Chr(13)"

Duane
Hook'D on Access
MS Access MVP

 

[pmcgreevy]

I tried adding the primary key: Didn't work.

Tried removing the field, closing the database, reopening the database and re-adding the CR field: Didn't help either.

 

[genomon]

That is because the graphical representation chr(13) is seven characters long.

Totally missed the boat there - I thought the Char was enclosed in double quotes to be used literally in an SQL statement. I had the same success as Duane mentioned above. I couldn't find CR in the list of Access reserved words, but you might try re-naming the field anyway and re-trying. Otherwise if the mdb isn't too big, you could try creating from scratch on a 2003 install instead of downsizing from 2007.

I do not get an error, but the field doesn't update either.

What are you doing to verify the field is not updated?
How are you trying to "look at" a carriage return?


Cogito eggo sum – I think, therefore I am a waffle.

 

[pmcgreevy]

If the field is updated, it will show a non-printable character in the field as a square.

The same thing will show in that field when I hit <CTRL>+<ENTER>, if it works.

If I try ?ASC(CR) in immediate mode, I still get nothing, since there is nothing for the function to return. It should return 13.

Also, the whole SQL string is enclosed in double quotes and works great for posting that day's date, as in:
Currentdb.Execute "Update [ajh82Work] Set [Ajh82Work].[Date_Ent] = Date()".

Didn't think to try it until this evening. Tomorrow, I will try [Ajh82Work]![CR} instead of [Ajh82Work].[CR].

I don't see it making a difference, but you never know.

I will post results of this try here, tomorrow.

Pat

 

[dhookom]

If the field is updated, it will show a non-printable character in the field as a square

My table didn't display a little square in datasheet view. I double the height of each ros and noticed there were was a carriage return in the field.

Duane
Hook'D on Access
MS Access MVP

 

[pmcgreevy]

Tried the '!' instead of '.' separating the table name and field name. Didn't help.

Tried posting an alpha character to the field and it works fine.

I was wrong about what the cr should look like if it updates. It should be a box with a '?' in it. If I export the table to a flat file and open it with the command line edit, it will show as a musical note at the end of the record.

I increased the size of the field to view it as dhookom offered, but there is nothing there.

I also turned off the Unicode compression, but that didn't do anything either.

I've been doing this for 15 years in VFP with no problems, but why this field will accept an alpha char and not a chr(), is beyond me.

Pat