Query Question...

[116]

I have this expression to get all data before "50 underscores"
Expr1:
Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2)
& "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'")

ie:
First Line "Blah" 'CR'
Second Line ___________ 'CR'
Third Line "Blah"

The text after the underline is getting replaced by 'H.T. 01'. I seem to be
getting additional Chariage Returns left behind. I have checked the text,
and there is only one. Yet when I run the update query I end up with
additinal ones.

David

 

[116]

Additional question...can the 'CR' be added as part of the 50 underscore
search?

David

 

[John W. Vinson]

I have this expression to get all data before "50 underscores"
Expr1:
Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2)
& "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'")

ie:
First Line "Blah" 'CR'
Second Line ___________ 'CR'
Third Line "Blah"

The text after the underline is getting replaced by 'H.T. 01'. I seem to be
getting additional Chariage Returns left behind. I have checked the text,
and there is only one. Yet when I run the update query I end up with
additinal ones.

David

I don't understand the question, 116. You're making a statement about an
expression (which you did post) and some query (which you didn't). What's the
context? What's the complete SQL of the query? What are you trying to
accomplish?

 

[116]

Sorry for the confussion. We have many operations containing text. Text
that very rarely changes is bove the 50 underscores, and text below can
change much more frequently. So I search for anything above the underscores
to keep, and I dlookup the text to replace below the underscores. It appears
that the first time I run the update query it works fine, but on subsequent,
the is a residual carriage return. So I was thinking if I search for the
'CR' followed by the underscores may eliminate the issue. Here is the sql as
requested.

UPDATE (Job_Operation INNER JOIN Job ON Job_Operation.Job = Job.Job) INNER
JOIN q_STD_FnR_HT_STD_Text_s1 ON Job.Job = q_STD_FnR_HT_STD_Text_s1.Job SET
Job_Operation.Note_Text =
Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2)
& "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'")
WHERE (((Job.Job) Like "T*") AND ((Job_Operation.Operation_Service) Like
"H.T.*"));

David

I have this expression to get all data before "50 underscores"
Expr1:
Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2)
& "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'")

ie:
First Line "Blah" 'CR'
Second Line ___________ 'CR'
Third Line "Blah"

The text after the underline is getting replaced by 'H.T. 01'. I seem to be
getting additional Chariage Returns left behind. I have checked the text,
and there is only one. Yet when I run the update query I end up with
additinal ones.

David

I don't understand the question, 116. You're making a statement about an
expression (which you did post) and some query (which you didn't). What's the
context? What's the complete SQL of the query? What are you trying to
accomplish?

 

[John W. Vinson]

Sorry for the confussion. We have many operations containing text. Text
that very rarely changes is bove the 50 underscores, and text below can
change much more frequently. So I search for anything above the underscores
to keep, and I dlookup the text to replace below the underscores. It appears
that the first time I run the update query it works fine, but on subsequent,
the is a residual carriage return. So I was thinking if I search for the
'CR' followed by the underscores may eliminate the issue. Here is the sql as
requested.

It certainly sounds like your table design needs reconsidreation! You're
storing two different kinds of data in this Note_Text field, visually
separating them by the underscores. Might it not be better to have *two
fields*, one for the static information and another for the variable? Or even
better, *two tables* related one to many, the first with a field for the
static information and the second a historical table with a new record
whenever you change the variable data??

Even worse, it seems that you're storing this massive text redundantly, in
both the Job table and the Job_Operation table. Why? What's the relationship
between the tables? How are you viewing this data?

UPDATE (Job_Operation INNER JOIN Job ON Job_Operation.Job = Job.Job) INNER
JOIN q_STD_FnR_HT_STD_Text_s1 ON Job.Job = q_STD_FnR_HT_STD_Text_s1.Job SET
Job_Operation.Note_Text =
Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2)
& "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'")
WHERE (((Job.Job) Like "T*") AND ((Job_Operation.Operation_Service) Like
"H.T.*"));

I don't see why this would introduce an extra carriage return unless it's
already in the Note_Text field in the Operation table; if there is, be aware
that a carriage return is actually stored as two characters, an ASCII 13
carriage return followed by an ASCII 10 Linefeed. So you might be able to use
the Left and Len functions to store all but the last two characters. I'd
*REALLY* advise rationalizing your table design instead, though!

 

[116]

Unfortunately its not me. The operation table has all the info relating to
the operation, but once an operation is added, the STD Text for that
operation becomes its own identity. So the term STD Text no longer applies.
We add additional info to that once it is part of the job. Ofcourse the
problem is when the operation STD Text changes. Hundreds of Template jobs
with the change operation STD Text require updating. I have been successful
simple updating 1 to 1, but this issue has cropped up. So now ALL my
operations have been updated with the '50 underscores' to seperate operation
STD Text from specific job info. The script I ran works fine on the first
run, but leaves the 'CR' behind on each subsequent run. I changed my script
to include Chr(10) & Chr(13) with the 50 Underscores. So far so good. Our
SQL DB has an ACCESS DB that I can access and create querys, in this case an
'Update Query'.

David

Sorry for the confussion. We have many operations containing text. Text
that very rarely changes is bove the 50 underscores, and text below can
change much more frequently. So I search for anything above the underscores
to keep, and I dlookup the text to replace below the underscores. It appears
that the first time I run the update query it works fine, but on subsequent,
the is a residual carriage return. So I was thinking if I search for the
'CR' followed by the underscores may eliminate the issue. Here is the sql as
requested.

It certainly sounds like your table design needs reconsidreation! You're
storing two different kinds of data in this Note_Text field, visually
separating them by the underscores. Might it not be better to have *two
fields*, one for the static information and another for the variable? Or even
better, *two tables* related one to many, the first with a field for the
static information and the second a historical table with a new record
whenever you change the variable data??

Even worse, it seems that you're storing this massive text redundantly, in
both the Job table and the Job_Operation table. Why? What's the relationship
between the tables? How are you viewing this data?

UPDATE (Job_Operation INNER JOIN Job ON Job_Operation.Job = Job.Job) INNER
JOIN q_STD_FnR_HT_STD_Text_s1 ON Job.Job = q_STD_FnR_HT_STD_Text_s1.Job SET
Job_Operation.Note_Text =
Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2)
& "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'")
WHERE (((Job.Job) Like "T*") AND ((Job_Operation.Operation_Service) Like
"H.T.*"));

I don't see why this would introduce an extra carriage return unless it's
already in the Note_Text field in the Operation table; if there is, be aware
that a carriage return is actually stored as two characters, an ASCII 13
carriage return followed by an ASCII 10 Linefeed. So you might be able to use
the Left and Len functions to store all but the last two characters. I'd
*REALLY* advise rationalizing your table design instead, though!

 

[John W. Vinson]

Unfortunately its not me. The operation table has all the info relating to
the operation, but once an operation is added, the STD Text for that
operation becomes its own identity. So the term STD Text no longer applies.
We add additional info to that once it is part of the job. Ofcourse the
problem is when the operation STD Text changes. Hundreds of Template jobs
with the change operation STD Text require updating. I have been successful
simple updating 1 to 1, but this issue has cropped up. So now ALL my
operations have been updated with the '50 underscores' to seperate operation
STD Text from specific job info. The script I ran works fine on the first
run, but leaves the 'CR' behind on each subsequent run. I changed my script
to include Chr(10) & Chr(13) with the 50 Underscores. So far so good. Our
SQL DB has an ACCESS DB that I can access and create querys, in this case an
'Update Query'.

A basic principle of relational database design is "atomicity": one field, one
fact.

You are using this field to store *two different facts*, and handling them in
two different ways.

Wouldn't it be easier if, rather than one field with a line of fifty
undescores in the middle of it, you had *two fields* which could be edited
independently, and then displayed or printed concatenated together if needed?

 

[116]

Thanks for the assist John. I did however realize the 2 spaces, but my
expression didn't account for it. Once I added Chr(10) to the expression
this resolved the issue. This situation is not a good one I realize, but
where it is not my database, I need to be creative to be able to update what
they call STD Text.

David

 

[De Jager]

I have this expression to get all data before "50 underscores"
Expr1:
Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2)
& "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'")

ie:
First Line "Blah" 'CR'
Second Line ___________ 'CR'
Third Line "Blah"

The text after the underline is getting replaced by 'H.T. 01'. I seem to
be
getting additional Chariage Returns left behind. I have checked the text,
and there is only one. Yet when I run the update query I end up with
additinal ones.

David

I don't understand the question, 116. You're making a statement about an
expression (which you did post) and some query (which you didn't). What's
the
context? What's the complete SQL of the query? What are you trying to
accomplish?

 

[joelgeraldine]

,,nn,,