Update Query

  • Thread starter Thread starter SeRene
  • Start date Start date
S

SeRene

Hi,
I need to update the below 6 tables with the same report
date. The sql code below doesnt seem to work if one of the
tables doesnt have any records for it to update.
So if one of the tables do not have ANY records, it
wouldnt update the other tables as well.

Can some1 tell me how i can edit the sql codes below so
that even if one of the tables do not have any records,
those tables with records will still be updated with the
date.

Thanks!

SQL Code---->
UPDATE tbl1, ttbl2, tbl3, tbl4, tbl5, tbl6 SET
tbl1.reportdate = [Enter Report Date:], tbl2.reportdate =
[Enter Report Date:], tbl3.reportdate = [Enter Report
Date:], tbl4.reportdate = [Enter Report Date:],
tbl5.reportdate = [Enter Report Date:], tbl6.reportdate =
[Enter Report Date:];
 
Why not use VB code and run each SQL statement
individually???

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

DoCmd.RunSQL "UPDATE tbl1 set tbl1.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl2 set tbl2.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl3 set tbl3.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl4 set tbl4.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl5 set tbl5.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl6 set tbl6.reportdate = RptDate;"

Xcelsoft
 
Hi,

I've tried those codes. The only problem is, there will be
7 input boxes in total!
How can i remove the other 6 input boxes??
I only need one input box!

Thanks.

Why not use VB code and run each SQL statement
individually???

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

DoCmd.RunSQL "UPDATE tbl1 set tbl1.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl2 set tbl2.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl3 set tbl3.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl4 set tbl4.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl5 set tbl5.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl6 set tbl6.reportdate = RptDate;"

Xcelsoft



-----Original Message-----
Hi,
I need to update the below 6 tables with the same report
date. The sql code below doesnt seem to work if one of the
tables doesnt have any records for it to update.
So if one of the tables do not have ANY records, it
wouldnt update the other tables as well.

Can some1 tell me how i can edit the sql codes below so
that even if one of the tables do not have any records,
those tables with records will still be updated with the
date.

Thanks!

SQL Code---->
UPDATE tbl1, ttbl2, tbl3, tbl4, tbl5, tbl6 SET
tbl1.reportdate = [Enter Report Date:], tbl2.reportdate =
[Enter Report Date:], tbl3.reportdate = [Enter Report
Date:], tbl4.reportdate = [Enter Report Date:],
tbl5.reportdate = [Enter Report Date:], tbl6.reportdate =
[Enter Report Date:];


.
Click to expand...
.
Click to expand...
 
I don't follow your question. The code below declares
the variable RptDate and sets the value to what's entered
in the InputBox only once. Then you update each of the
six tables to the value of the variable RptDate.

I don't understand where you are getting th 7 input boxes.

You only do this one time before the update statements.

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

Xcelsoft

----------------------------------------------------------

Original Message-----
Hi,

I've tried those codes. The only problem is, there will be
7 input boxes in total!
How can i remove the other 6 input boxes??
I only need one input box!

Thanks.

Why not use VB code and run each SQL statement
individually???

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

DoCmd.RunSQL "UPDATE tbl1 set tbl1.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl2 set tbl2.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl3 set tbl3.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl4 set tbl4.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl5 set tbl5.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl6 set tbl6.reportdate = RptDate;"

Xcelsoft



-----Original Message-----
Hi,
I need to update the below 6 tables with the same report
date. The sql code below doesnt seem to work if one of the
tables doesnt have any records for it to update.
So if one of the tables do not have ANY records, it
wouldnt update the other tables as well.

Can some1 tell me how i can edit the sql codes below so
that even if one of the tables do not have any records,
those tables with records will still be updated with the
date.

Thanks!

SQL Code---->
UPDATE tbl1, ttbl2, tbl3, tbl4, tbl5, tbl6 SET
tbl1.reportdate = [Enter Report Date:],
Click to expand...
tbl2.reportdate
=
[Enter Report Date:], tbl3.reportdate = [Enter Report
Date:], tbl4.reportdate = [Enter Report Date:],
tbl5.reportdate = [Enter Report Date:],
Click to expand...
tbl6.reportdate
=
[Enter Report Date:];


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
The problem is because the variable name is inside the quotes.

You need:

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

DoCmd.RunSQL "UPDATE tbl1 set tbl1.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl2 set tbl2.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl3 set tbl3.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl4 set tbl4.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl5 set tbl5.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl6 set tbl6.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")

The cryptic argument in the Format function is to ensure that the dates are
delimited with #, and that they're in mm/dd/yyyy format (a must, regardless
of what the short date format has been set to through regional settings)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Xcelsoft said:
I don't follow your question. The code below declares
the variable RptDate and sets the value to what's entered
in the InputBox only once. Then you update each of the
six tables to the value of the variable RptDate.

I don't understand where you are getting th 7 input boxes.

You only do this one time before the update statements.

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

Xcelsoft

----------------------------------------------------------

Original Message-----
Hi,

I've tried those codes. The only problem is, there will be
7 input boxes in total!
How can i remove the other 6 input boxes??
I only need one input box!

Thanks.

Why not use VB code and run each SQL statement
individually???

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

DoCmd.RunSQL "UPDATE tbl1 set tbl1.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl2 set tbl2.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl3 set tbl3.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl4 set tbl4.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl5 set tbl5.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl6 set tbl6.reportdate = RptDate;"

Xcelsoft




-----Original Message-----
Hi,
I need to update the below 6 tables with the same report
date. The sql code below doesnt seem to work if one of
the
tables doesnt have any records for it to update.
So if one of the tables do not have ANY records, it
wouldnt update the other tables as well.

Can some1 tell me how i can edit the sql codes below so
that even if one of the tables do not have any records,
those tables with records will still be updated with the
date.

Thanks!

SQL Code---->
UPDATE tbl1, ttbl2, tbl3, tbl4, tbl5, tbl6 SET
tbl1.reportdate = [Enter Report Date:], tbl2.reportdate
=
[Enter Report Date:], tbl3.reportdate = [Enter Report
Date:], tbl4.reportdate = [Enter Report Date:],
tbl5.reportdate = [Enter Report Date:], tbl6.reportdate
=
[Enter Report Date:];


.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Correct Douglas,

I assumed the user was going to read the variable
properly. I just wanted to show where to place the
variable but I should have displayed it the way it
actually will be read along with the Format function.

Xcelsoft
-----Original Message-----
The problem is because the variable name is inside the quotes.

You need:

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

DoCmd.RunSQL "UPDATE tbl1 set tbl1.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl2 set tbl2.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl3 set tbl3.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl4 set tbl4.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl5 set tbl5.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL "UPDATE tbl6 set tbl6.reportdate = " & _
Format$(RptDate, "\#mm\/dd\/yyyy\#")

The cryptic argument in the Format function is to ensure that the dates are
delimited with #, and that they're in mm/dd/yyyy format (a must, regardless
of what the short date format has been set to through regional settings)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I don't follow your question. The code below declares
the variable RptDate and sets the value to what's entered
in the InputBox only once. Then you update each of the
six tables to the value of the variable RptDate.

I don't understand where you are getting th 7 input boxes.

You only do this one time before the update statements.

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

Xcelsoft

------------------------------------------------------- ---

Original Message-----
Hi,

I've tried those codes. The only problem is, there
Click to expand...
will
be
7 input boxes in total!
How can i remove the other 6 input boxes??
I only need one input box!

Thanks.


Why not use VB code and run each SQL statement
individually???

Dim RptDate as DATE
RptDate = InPutBox("Enter Report Date:")

DoCmd.RunSQL "UPDATE tbl1 set tbl1.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl2 set tbl2.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl3 set tbl3.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl4 set tbl4.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl5 set tbl5.reportdate = RptDate;"
DoCmd.RunSQL "UPDATE tbl6 set tbl6.reportdate = RptDate;"

Xcelsoft




-----Original Message-----
Hi,
I need to update the below 6 tables with the same report
date. The sql code below doesnt seem to work if one of
the
tables doesnt have any records for it to update.
So if one of the tables do not have ANY records, it
wouldnt update the other tables as well.

Can some1 tell me how i can edit the sql codes below so
that even if one of the tables do not have any records,
those tables with records will still be updated with the
date.

Thanks!

SQL Code---->
UPDATE tbl1, ttbl2, tbl3, tbl4, tbl5, tbl6 SET
tbl1.reportdate = [Enter Report Date:], tbl2.reportdate
=
[Enter Report Date:], tbl3.reportdate = [Enter Report
Date:], tbl4.reportdate = [Enter Report Date:],
tbl5.reportdate = [Enter Report Date:], tbl6.reportdate
=
[Enter Report Date:];


.

.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top