Shelf Life

  • Thread starter Thread starter Gary F Shelton
  • Start date Start date
G

Gary F Shelton

NOTE: I have a field called SHLF_Life and in it there are some null fields. I
want to replace the null fields with 365. I have wrote a statement and it
works but everytime I run the query I get a prompt on my screen asking me to
input the paramter value for SHLF_LF. I do not type anything in but only
click ok. What have I done wrong?

SHLF_LF_Days: IIf([SHLF_LF] Is Null,365,[SHLF_LF])
 
seems not to be able to determine SHLF_LF

trust that you don't have multiple SHLF_LF named fields or that there is no
criteria in the SHLF_LF field...

if not too much work you might just whip up a new query from scratch just a
sanity check. seems to me I experienced this once and rather than solve I
simply restarted from scratch .... of course one must take care with a new
query name should other objects be sourced to the old name...
 
NOTE: I have a field called SHLF_Life and in it there are some null fields. I
want to replace the null fields with 365. I have wrote a statement and it
works but everytime I run the query I get a prompt on my screen asking me to
input the paramter value for SHLF_LF. I do not type anything in but only
click ok. What have I done wrong?

SHLF_LF_Days: IIf([SHLF_LF] Is Null,365,[SHLF_LF])
Click to expand...

Um? No IIF is needed at all: try this query (copy and paste the SQL into the
SQL window of a new query and adjust your tablename):

UPDATE yourtable SET [SHLF_LF] = 365 WHERE [SHLF_LF] IS NULL;

This will work if yourtable actually has a field named SHLF_LF - possibly you
have a misspelling, such as the field actually being named [SHLF LF]???
 
thanks thus far for the solutions but they haven't worked. Maybe I need to
provide more background so someone can point out the error in my ways. :)

The lot date (Date the product (sku) is made come in as text as the
following format: 20081031. I in turn created some left, mid, and right
statements to parse the data out and then I concatonated the data as a CDATE
query to turn it into 10/31/2008 (NOTE: it is a date/time field now...) Then
I have a table of sku's with the shelf life but some of the records are null.

In my new query is where I match sku to sku and it puts the correct shelf
life in where needed but it was the null field that I was trying to replace
with 365 all in the same query... So maybe what I am learning is that I just
need to write a new query to do the next step as I might be trying to do to
many steps in one query....

--
GS


John W. Vinson said:
NOTE: I have a field called SHLF_Life and in it there are some null fields. I
want to replace the null fields with 365. I have wrote a statement and it
works but everytime I run the query I get a prompt on my screen asking me to
input the paramter value for SHLF_LF. I do not type anything in but only
click ok. What have I done wrong?

SHLF_LF_Days: IIf([SHLF_LF] Is Null,365,[SHLF_LF])
Click to expand...

Um? No IIF is needed at all: try this query (copy and paste the SQL into the
SQL window of a new query and adjust your tablename):

UPDATE yourtable SET [SHLF_LF] = 365 WHERE [SHLF_LF] IS NULL;

This will work if yourtable actually has a field named SHLF_LF - possibly you
have a misspelling, such as the field actually being named [SHLF LF]???
Click to expand...
 
thanks thus far for the solutions but they haven't worked. Maybe I need to
provide more background so someone can point out the error in my ways. :)

The lot date (Date the product (sku) is made come in as text as the
following format: 20081031. I in turn created some left, mid, and right
statements to parse the data out and then I concatonated the data as a CDATE
query to turn it into 10/31/2008 (NOTE: it is a date/time field now...) Then
I have a table of sku's with the shelf life but some of the records are null.

In my new query is where I match sku to sku and it puts the correct shelf
life in where needed but it was the null field that I was trying to replace
with 365 all in the same query... So maybe what I am learning is that I just
need to write a new query to do the next step as I might be trying to do to
many steps in one query....
Click to expand...

I'm sorry, Gary. This is not coming through.

You have a LotDate field that used to be text, and is now a date/time field?

You say "some of the records are null". The whole record? the sku? the
lotdate? Is there a separate shelflife field? What is its datatype? What is
"the new query" - could you post the SQL of the query?
 
Back
Top