Update query based on another query. (1 Viewer)

GregoryWest

Registered User.
Local time
Today, 09:41
Joined
Apr 13, 2014
Messages
161
I am trying to write an update query. There are 1/2 dozen to a couple dozen fields in the various tables in question. The raw data is sitting in an access table with 4 fields:
ID automatic ID
Text1 Char(255) first half of the import files line
Text2 Char(255) second half of the import files line


There are queries called rawdata_01 (to 06) that calls up this table and adds a calculated field:
RecType Number(2) defined as =mid(text1,14,2) and a condition (= 1 to 6))


The update to the working tables updates each field with the 'update to:' set to a series of mid statements that look like: Mid(([RawData_01]!InputText),1,3)


My problem is instead of updating the data in the working table, Access is asking me for the value of RawData_01!InputText


What am I doing wrong? Or is what I am trying to do not possible.


Now, just a caviot, please don't ask me about changing the raw data as I do not control the source in any way. I am sent a flat text file of several thousand records with one of 6 different record structures possible for each individual line.
 

MarkK

bit cruncher
Local time
Today, 07:41
Joined
Mar 17, 2004
Messages
8,178
I don't believe that bang character is legal in SQL, but also, if I was stuck with a query, I would show my SQL text. If you ask, "What am I doing wrong?" but you haven't shown your work, we don't really have enough info to comment.
hth
Mark
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:41
Joined
Feb 28, 2001
Messages
27,003
Almost invariably, when Access asks for the value of some field, the problem is either (a) something is spelled wrong or (b) the item you named is not a member of the thing that you referenced in order to find it. And (a) could apply to the field or its container equally well.
 

GregoryWest

Registered User.
Local time
Today, 09:41
Joined
Apr 13, 2014
Messages
161
Here is the SQL that is giving me the grief.
UPDATE Roll SET Roll.[muni-no] = Mid(([RawData_01]![InputText]),1,3), Roll.[roll-no-type] = Mid([RawData_01]!InputText,4,1), Roll.[roll-no] = Mid([RawData_01]!InputText,5,9), Roll.[record-type] = Mid([RawData_01]!InputText,14,2), Roll.ward = Mid([RawData_01]!InputText,16.3), Roll.hospital = Mid([RawData_01]!InputText,19,3), Roll.school = Mid([RawData_01]!InputText,22,2), Roll.neighborhood = Mid([RawData_01]!InputText,24.4), Roll.rhota = Mid([RawData_01]!InputText,25,1), Roll.psta = Mid([RawData_01]!InputText,26,1), Roll.[survey-desc] = Mid([RawData_01]!InputText,27,30), Roll.[more-survey] = Mid([RawData_01]!InputText,57,1), Roll.ownership = Mid([RawData_01]!InputText,58,4), Roll.dwellings = Mid([RawData_01]!InputText,62,3), Roll.frontage = Mid([RawData_01]!InputText,65,7), Roll.[frontage-um] = Mid([RawData_01]!InputText,72,1), Roll.addr = Mid([RawData_01]!InputText,73,30), Roll.lud = Mid([RawData_01]!InputText,103,3), Roll.community = Mid([RawData_01]!InputText,106,3);



The query that makes up RawData_01 (which works fine) is:
SELECT [Raw-Data-In].*, Mid$([Raw-Data-In]![InputText],14,2) AS type
FROM [Raw-Data-In]
WHERE (((Mid$([Raw-Data-In]![InputText],14,2))=1));
 

MarkK

bit cruncher
Local time
Today, 07:41
Joined
Mar 17, 2004
Messages
8,178
There is no FROM clause in your UPDATE SQL. As a result the table RawData_01 does not exist and the identifier "RawData_01!InputText" is assumed to be a parameter, which the query parser then prompts you for.

Also, change the bang (!) character to a period (.) :)

hth
Mark
 

GregoryWest

Registered User.
Local time
Today, 09:41
Joined
Apr 13, 2014
Messages
161
Hi guys, thanks for all your assistance, in the end I am going to blame it of lack of coffee! The reason I was having so much trouble with the 'update' command, was simple, I needed to use the APPEND command! Now everything is working quite smoothly.
 

Users who are viewing this thread

Top Bottom