help with an update query..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

good day all,

I have an update query based on queryshipmentrequest, I have the following
relevant fields in my query:

shipmentrequest yes/no checkbox
Shipmentrequestdate
rugstatus

I want the update to see if shipmentrequest is true and shipmentrequestdate
is false , then update shipmentrequest to Now() and rugstatus to "In-Transit".

Can anyone help me?

Thanks,

Brook

Here is my current code that isn't working:

UPDATE qryshipmentrequest SET qryshipmentrequest.RugStatus = "In-Transit",
qryshipmentrequest.shipmentrequestdate = Now()
WHERE (((qryshipmentrequest.shipmentrequestdate)=False) AND
((qryshipmentrequest.shipmentrequest)=True));
 
Brook said:
good day all,

I have an update query based on queryshipmentrequest, I have the following
relevant fields in my query:

shipmentrequest yes/no checkbox
Shipmentrequestdate
rugstatus

I want the update to see if shipmentrequest is true and shipmentrequestdate
is false , then update shipmentrequest to Now() and rugstatus to "In-Transit".

Can anyone help me?

Thanks,

Brook

Here is my current code that isn't working:

UPDATE qryshipmentrequest SET qryshipmentrequest.RugStatus = "In-Transit",
qryshipmentrequest.shipmentrequestdate = Now()
WHERE (((qryshipmentrequest.shipmentrequestdate)=False) AND
((qryshipmentrequest.shipmentrequest)=True));
Click to expand...

Brook,

A date (field) is not TRUE or FALSE; it will be NULL or NOT NULL.

If I understand right, you want to update [rugstatus] and
[Shipmentrequestdate] if [shipmentrequest] is TRUE and *no* date has been
entered in [Shipmentrequestdate].

Here is the SQL for the query:

UPDATE qryshipmentrequest SET qryshipmentrequest.rugstatus = "In-Transit",
qryshipmentrequest.Shipmentrequestdate = Date()
WHERE qryshipmentrequest.Shipmentrequestdate Is Null AND
qryshipmentrequest.shipmentrequest=True;


If you want records where [Shipmentrequestdate] *has* a date and
[shipmentrequest] is TRUE, change "IS NULL" to "IS NOT NULL".

Also note that Now() is the date AND time (ie 1/2/2006 11:32 AM).
If you want only the date, use DATE() (ie 1/2/2006)

HTH
 
Steve,

Thank you very much... that is what I was looking for..

Brook

SteveS said:
Brook said:
good day all,

I have an update query based on queryshipmentrequest, I have the following
relevant fields in my query:

shipmentrequest yes/no checkbox
Shipmentrequestdate
rugstatus

I want the update to see if shipmentrequest is true and shipmentrequestdate
is false , then update shipmentrequest to Now() and rugstatus to "In-Transit".

Can anyone help me?

Thanks,

Brook

Here is my current code that isn't working:

UPDATE qryshipmentrequest SET qryshipmentrequest.RugStatus = "In-Transit",
qryshipmentrequest.shipmentrequestdate = Now()
WHERE (((qryshipmentrequest.shipmentrequestdate)=False) AND
((qryshipmentrequest.shipmentrequest)=True));
Click to expand...

Brook,

A date (field) is not TRUE or FALSE; it will be NULL or NOT NULL.

If I understand right, you want to update [rugstatus] and
[Shipmentrequestdate] if [shipmentrequest] is TRUE and *no* date has been
entered in [Shipmentrequestdate].

Here is the SQL for the query:

UPDATE qryshipmentrequest SET qryshipmentrequest.rugstatus = "In-Transit",
qryshipmentrequest.Shipmentrequestdate = Date()
WHERE qryshipmentrequest.Shipmentrequestdate Is Null AND
qryshipmentrequest.shipmentrequest=True;


If you want records where [Shipmentrequestdate] *has* a date and
[shipmentrequest] is TRUE, change "IS NULL" to "IS NOT NULL".

Also note that Now() is the date AND time (ie 1/2/2006 11:32 AM).
If you want only the date, use DATE() (ie 1/2/2006)

HTH
Click to expand...
 
Steve,

maybe you can help me with something else along the same route... I have
posted a message in the Report section, but how do I open a report from a
form if the checkbox (shipmentrequest) is true?

Brook

SteveS said:
Brook said:
good day all,

I have an update query based on queryshipmentrequest, I have the following
relevant fields in my query:

shipmentrequest yes/no checkbox
Shipmentrequestdate
rugstatus

I want the update to see if shipmentrequest is true and shipmentrequestdate
is false , then update shipmentrequest to Now() and rugstatus to "In-Transit".

Can anyone help me?

Thanks,

Brook

Here is my current code that isn't working:

UPDATE qryshipmentrequest SET qryshipmentrequest.RugStatus = "In-Transit",
qryshipmentrequest.shipmentrequestdate = Now()
WHERE (((qryshipmentrequest.shipmentrequestdate)=False) AND
((qryshipmentrequest.shipmentrequest)=True));
Click to expand...

Brook,

A date (field) is not TRUE or FALSE; it will be NULL or NOT NULL.

If I understand right, you want to update [rugstatus] and
[Shipmentrequestdate] if [shipmentrequest] is TRUE and *no* date has been
entered in [Shipmentrequestdate].

Here is the SQL for the query:

UPDATE qryshipmentrequest SET qryshipmentrequest.rugstatus = "In-Transit",
qryshipmentrequest.Shipmentrequestdate = Date()
WHERE qryshipmentrequest.Shipmentrequestdate Is Null AND
qryshipmentrequest.shipmentrequest=True;


If you want records where [Shipmentrequestdate] *has* a date and
[shipmentrequest] is TRUE, change "IS NULL" to "IS NOT NULL".

Also note that Now() is the date AND time (ie 1/2/2006 11:32 AM).
If you want only the date, use DATE() (ie 1/2/2006)

HTH
Click to expand...
 
See your post in access.reports
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Brook said:
Steve,

maybe you can help me with something else along the same route... I have
posted a message in the Report section, but how do I open a report from a
form if the checkbox (shipmentrequest) is true?

Brook

SteveS said:
Brook said:
good day all,

I have an update query based on queryshipmentrequest, I have the following
relevant fields in my query:

shipmentrequest yes/no checkbox
Shipmentrequestdate
rugstatus

I want the update to see if shipmentrequest is true and shipmentrequestdate
is false , then update shipmentrequest to Now() and rugstatus to "In-Transit".

Can anyone help me?

Thanks,

Brook

Here is my current code that isn't working:

UPDATE qryshipmentrequest SET qryshipmentrequest.RugStatus = "In-Transit",
qryshipmentrequest.shipmentrequestdate = Now()
WHERE (((qryshipmentrequest.shipmentrequestdate)=False) AND
((qryshipmentrequest.shipmentrequest)=True));
Click to expand...

Brook,

A date (field) is not TRUE or FALSE; it will be NULL or NOT NULL.

If I understand right, you want to update [rugstatus] and
[Shipmentrequestdate] if [shipmentrequest] is TRUE and *no* date has been
entered in [Shipmentrequestdate].

Here is the SQL for the query:

UPDATE qryshipmentrequest SET qryshipmentrequest.rugstatus = "In-Transit",
qryshipmentrequest.Shipmentrequestdate = Date()
WHERE qryshipmentrequest.Shipmentrequestdate Is Null AND
qryshipmentrequest.shipmentrequest=True;


If you want records where [Shipmentrequestdate] *has* a date and
[shipmentrequest] is TRUE, change "IS NULL" to "IS NOT NULL".

Also note that Now() is the date AND time (ie 1/2/2006 11:32 AM).
If you want only the date, use DATE() (ie 1/2/2006)

HTH
Click to expand...
Click to expand...
 
Back
Top