Solved

Handling Queries in VBA Code

Posted on 2004-09-15
9
264 Views
Last Modified: 2006-11-17
Hi, can anyone help me please?

I need some code that will enable me to modify two fields of certain multiple records in a table. The trigger will be from the yes button of a msgbox.

My first idea was to build a query to select the records, but then I don't know how to update the values in the query. Also, how can I dynamically send criteria to the query from the code?

Thanks in advance!
0
Comment
Question by:PJRimmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 12063224
You need to use an UPDATE query.

If you can give examples of your table name, fields required and when the values would be changed, we can show you how to build the queryand incorporate into VBA
0
 

Author Comment

by:PJRimmer
ID: 12063247
I've done a bit more work on this and I am trying to do the following:

DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = True, tbl_VantageCharging.EndDate = Now()" & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = False);"

Seems to be almost there, but it is complaining about a data type mismatch in the criteria. Any ideas? Am I on the right track?
0
 

Author Comment

by:PJRimmer
ID: 12063271
Sorry, missed a couple of lines. It's all part of a msgbox!

        If MsgBox("Do you want to end all current charges and start a new charge(s) for the new package?", vbYesNo + vbQuestion, "Change Charges?") = vbYes Then
            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = True, tbl_VantageCharging.EndDate = Now()" & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = False);"
        Else
        End If

Cheers!
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 26

Expert Comment

by:dannywareham
ID: 12063274
You've answered your own question!

As for teh data-type mismatch, this will be caused because the field "Ended?" or "EndDate" in table "tbl_VantageCharging" will only accept certain types of values (for example, a number field will not accept text).

Check teh type of data being added (change TRUE to -1 for example)
0
 

Author Comment

by:PJRimmer
ID: 12063440
Hey I was a bit hasty asking the question!

I have been messing around trying different things, but I sill get data type mismatch.

            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = '-1', tbl_VantageCharging.EndDate = '" & Date & "' " & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = '0');"

Any advice of use of brackets, '&' and quote marks?
0
 
LVL 2

Expert Comment

by:Hakim
ID: 12063485
my 2 cents!

There is no need of inclosing -1 and 0.
0
 

Author Comment

by:PJRimmer
ID: 12063692
Yeah I tried that but I still get the same error:

Run-time error '3464':

Data type mismatch in criteria expression.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 250 total points
ID: 12063732
If any of the fields are text data types, you need to enclose the values that you are updating to with ' characters. If they are numerics, you don't, and dates use the # delimiter. In this example below, I'm assuming Ended is a numeric (boolean) field, EndDate is a Date/Time field and AccountNo is a text field:

            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = -1, tbl_VantageCharging.EndDate = #" & Format(Date,"mm/dd/yyyy") & "# " & _
                "WHERE (tbl_VantageCharging.AccountNo = '" & AccountNo & "' AND tbl_VantageCharging.[Ended?] = 0);"
0
 

Author Comment

by:PJRimmer
ID: 12064348
Thanks, that all works fine now!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question