CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Elliot Blackburn
Elliot Blackburn

Reputation: 4164

Updating a field dependent on a date range in Access with VisualBasic and SQL

A friend and I have been trying for hours with little progress to a get a piece of code right for an invoicing system we're designing as a project.

We are trying to update the field InvoiceNo to a value (worked out earlier in the VisualBasic code), where the CustomerNo is the is a specific value and the FinishDate is between two dates. At first I was trying to use TO_DATE but then we realized that wasn't the same in the SQL that Access uses (after much searching).

This has been the simple statement I've been using to just test and try to get something working to then translate into VisualBasic and put in our variables. It's a little easier to read so I thought I'd provide it.

UPDATE tblJob SET tblJob.InvoiceNo = '8' WHERE tblJob.CustomerNo = '1' AND (tblJob.FinishDate BETWEEN cdate(format('08/09/2013', '##/##/####')) AND cdate(format('03/10/2013', '##/##/####')));

I have a feeling after looking at a few examples that our date is meant to be without an forward slashes. So I tried that and it wasn't working either.

Here's the VisualBasic code that has come out of all of this, it's exactly the same but using some variables rather than our set values that I've been using for testing.

DoCmd.RunSQL ("UPDATE tblJob SET tblJob.InvoiceNo = '" & newInvoiceNo & "' WHERE tblJob.CustomerNo = '" & VbCustNo & "' AND (tblJob.FinishDate BETWEEN cdate(format('" & Forms![frmMainMenu][txtFirstDate] & "', '##/##/####')) AND cdate(format('" & Forms![frmMainmenu][txtEndDate] & "', '##/##/####')));")

We had a look at: Convert a string to a date in Access and it helped us realize that it was cdate(format()) rather than TO_DATE as it is in Oracle. But we just can't seem to get it to run properly, any help would be much appreciated.

Upvotes: 1

Views: 2244

Answers (1)

HansUp
HansUp

Reputation: 97101

If you will be running the query from within an Access application session, you can let the db engine use the Access expression service to grab the values from the text boxes on your form.

Dim db As DAO.Database
Dim strUpdate As String
strUpdate = "UPDATE tblJob" & vbCrLf & _
    "SET InvoiceNo = '" & newInvoiceNo & "'" & vbCrLf & _
    "WHERE CustomerNo = '" & VbCustNo & "'" & vbCrLf & _
    "AND FinishDate BETWEEN Forms!frmMainMenu!txtFirstDate AND Forms!frmMainmenu!txtEndDate;"
Debug.Print strUpdate
Set db = CurrentDb
db.Execute strUpdate, dbFailOnError
Set db = Nothing

However, if you prefer to build the literal date values from those text boxes into your UPDATE statement, you can use Format().

"AND FinishDate BETWEEN " & _
Format(Forms!frmMainmenu!txtFirstDate, "\#yyyy-m-d\#") & _
" AND " & Format(Forms!frmMainmenu!txtEndDate, "\#yyyy-m-d\#") & ";"

Either way, using a string variable to hold your UPDATE statement gives you an opportunity to examine the completed statement you're asking the db engine to execute.

You can view the output from Debug.Print in the Immediate window (go there with Ctl+g). For troubleshooting, you can copy the statement text from there and then paste it into SQL View of a new Access query.

Upvotes: 1

Related Questions