I need to change all data in two fields

[Guest]

I have a table holding all of our customers information. Two of the fields
are SocialSecuityNumber and DateOfBirth. For all 2500 customers, I need to
set the SS# to 000000000 and the DateOfBirth to 25413. Can you tell me how
to do this?

Thank you. Janine

 

[fredg]

I have a table holding all of our customers information. Two of the fields
are SocialSecuityNumber and DateOfBirth. For all 2500 customers, I need to
set the SS# to 000000000 and the DateOfBirth to 25413. Can you tell me how
to do this?

Thank you. Janine

Permanently?

25413 is 7/29/1969 as a date. Is that what you want?

Back up your data first.
Run an Update Query.

Update YourTable Set YourTable.[SSN] = "000000000",
YourTable.[DateOfBirth] = CDate(25413)

 

[MGFoster]

I have a table holding all of our customers information. Two of the fields
are SocialSecuityNumber and DateOfBirth. For all 2500 customers, I need to
set the SS# to 000000000 and the DateOfBirth to 25413. Can you tell me how
to do this?

UPDATE table_name
SET SocialSecurityNumber = '000000000',
DateofBirth = 24513

 

[Guest]

It worked perfectly! Thank you!!!! Janine

I have a table holding all of our customers information. Two of the fields
are SocialSecuityNumber and DateOfBirth. For all 2500 customers, I need to
set the SS# to 000000000 and the DateOfBirth to 25413. Can you tell me how
to do this?

Thank you. Janine

Permanently?

25413 is 7/29/1969 as a date. Is that what you want?

Back up your data first.
Run an Update Query.

Update YourTable Set YourTable.[SSN] = "000000000",
YourTable.[DateOfBirth] = CDate(25413)

 

[Afrosheen]

UPDATE table_name
SET SocialSecurityNumber = '000000000',
DateofBirth = 24513

looks good, but I need a routine that will change the data in one field if
the information in another field meets the critera. For example

If shift = "b" then
status = "off'
endif

That's dbase talk sorry.
Thanks

 

[Bob Barrows [MVP]]

looks good, but I need a routine that will change the data in one
field if the information in another field meets the critera. For
example

If shift = "b" then
status = "off'
endif

That's dbase talk sorry.
Thanks

UPDATE table_name
SET status = "off"
WHERE shift="b"

 

[Afrosheen]

UPDATE table_name
SET status = "off"
WHERE shift="b"
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


Thanks for the quick reply. Sorry though, it didn't work. Here is the complet routine I'm doing. I'm doing it {on click}. I'm trying to reset the status field to off day if the shift field is = to b-days then go in to the next menu.

Thanks for the help.

Private Sub A_Rotation_Click()
On Error GoTo Err_A_Rotation_Click

Update tbl_roster
Set Status = "off day"
WHERE Shift = "b-days"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_Roster"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_A_Rotation_Click:
Exit Sub

Err_A_Rotation_Click:
MsgBox Err.Description
Resume Exit_A_Rotation_Click

End Sub

 

[John W. Vinson]

Thanks for the help.

Private Sub A_Rotation_Click()
On Error GoTo Err_A_Rotation_Click

Update tbl_roster
Set Status = "off day"
WHERE Shift = "b-days"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_Roster"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_A_Rotation_Click:
Exit Sub

Err_A_Rotation_Click:
MsgBox Err.Description
Resume Exit_A_Rotation_Click

End Sub

You are mixing VBA syntax with SQL query language syntax. Both languages exist
in Access, *but they are different languages* and cannot be mixed in the way
you're doing.

Either execute a query as suggested, or open a Recordset based on the table
and loop through it.

What does opening a form have to do with executing an update query though???

 

[Bob Barrows [MVP]]

Thanks for the help.

Private Sub A_Rotation_Click()
On Error GoTo Err_A_Rotation_Click

Update tbl_roster
Set Status = "off day"
WHERE Shift = "b-days"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_Roster"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_A_Rotation_Click:
Exit Sub

Err_A_Rotation_Click:
MsgBox Err.Description
Resume Exit_A_Rotation_Click

End Sub

As John says, there are two languages involved: SQL and VBA. I provided a
sql statement that you could execute by creating a new query in the Queries
pane of the database window, switching to SQL View, typing it in, and
clicking the Execute button on the toolbar.

Since you seem to want to do this via a button click, you need to use ADO,
DAO or DoCmd.RunQuery to run this sql statement. Here is how to do it via
ADO:

Dim cn as ADODB.Connection,sql as string
Set cn = CurrentProject.AccessConnection
sql="Update tbl_roster Set Status = 'off day' " & _
"WHERE Shift = 'b-days'"
cn.execute sql,,adCmdText + adExecuteNoRecords
Set cn = nothing