How do I pass a value to an insert query

**Luisa** · 06-01-2017, 01:17 PM

I have been trying to work out an insert query, but I don't know how to provide it the value for "IntervalMonth" that changes with each loop by using DateAdd('m',IntervalMonth,[TermStartDate]). I get an error on IntervalMonth. Here is what I have done so far:

Private Sub Command35_Click()

Dim InvNo As Long
InvNo = 1
Dim InvDate As Date
InvDate = TermStartDate ' This is the first date invoices are created for

Dim IntervalType As String
IntervalType = "m" ' "m" specifies months as interval.

Dim IntervalMonth As Integer ' This is the interval to be added from the TermStartDate

IntervalMonth = 1

For InvNo = 1 To InvReq
Dim StrQuery As String
DoCmd.Echo False
DoCmd.SetWarnings False

StrQuery = "INSERT INTO Invoices ( Invoice_TenantID, Invoice_LeaseID, Invoice_BaseRent, Invoice_AdditionalRent, Invoice_Date )SELECT Tenant.Tenant_ID, LeaseTerm.Lease_ID, LeaseTerm.TermBaseRent, LeaseTerm.TermAdditionalRent, DateAdd('m',IntervalMonth,[TermStartDate]) AS InvDate FROM (Lease INNER JOIN LeaseTerm ON Lease.Lease_ID = LeaseTerm.Lease_ID) INNER JOIN Tenant ON Lease.Tenant_ID = Tenant.Tenant_ID WHERE (((LeaseTerm.LeaseTerm_ID)=[Forms]![CreateInvs]![LeaseTerm_ID]));"
DoCmd.RunSQL StrQuery

InvDate = DateAdd(IntervalType, IntervalMonth, TermStartDate)
IntervalMonth = IntervalMonth + 1

Next InvNo

DoCmd.Echo True
DoCmd.SetWarnings True
MsgBox ("Invoices have been created for the Lease Term")
End Sub

I really don't know how to go further... can anyone help me

,
Luisa

**pbaldy** · 06-01-2017, 01:30 PM

You have to concatenate the value into the string, like:

"...TermAdditionalRent, #" & DateAdd('m',IntervalMonth,[TermStartDate]) "# AS InvDate..."

**Micron** · 06-01-2017, 01:32 PM

two possible reasons. This looks like an example of a common trap where unwittingly you have included IntervalMonth as part of a literal string and not a variable to be passed to an expression (or sometimes a function). Think of it this way. The sql statement starts and ends with double quotes. Everything in between that is recognized as an object such as a table or field, or an Access 'command' such as INNER JOIN can be evaluated. A custom function or variable cannot - so you have to concatenate.
(removed example as it reflects the post that just beat me to it)
However, if the suggestion solves one issue, I think this could cause the same issue: =[Forms]![CreateInvs]![LeaseTerm_ID]))

Tip: Please use code tags around anything more than a few lines and use customary indentation. Code is much easier to read that way.

**Bulzie** · 06-01-2017, 01:34 PM

What is the error you are getting and where in the code are you getting the error?

**Luisa** · 06-01-2017, 03:10 PM

Originally Posted by pbaldy

You have to concatenate the value into the string, like:

"...TermAdditionalRent, #" & DateAdd('m',IntervalMonth,[TermStartDate]) "# AS InvDate..."

.

Thank you for the quick response. I tried your suggestion as it made sense to me, but I got a syntax error. Any suggestions

This is what I did:
Private Sub Command35_Click()

Dim InvNo As Long
InvNo = 1

Dim InvDate As Date
InvDate = TermStartDate ' This is the first date invoices are created for

Dim IntervalType As String
IntervalType = "m" ' "m" specifies months as interval.

Dim IntervalMonth As Integer ' This is the interval to be added from the TermStartDate
IntervalMonth = 1

For InvNo = 1 To InvReq
Dim StrQuery As String
DoCmd.Echo False
DoCmd.SetWarnings False

StrQuery = "INSERT INTO Invoices ( Invoice_TenantID, Invoice_LeaseID, Invoice_BaseRent, Invoice_AdditionalRent, Invoice_DateCreated, Invoice_Date )SELECT Tenant.Tenant_ID, LeaseTerm.Lease_ID, LeaseTerm.TermBaseRent, LeaseTerm.TermAdditionalRent, date(), #" & DateAdd('m',IntervalMonth,[TermStartDate]"# AS InvDate FROM (Lease INNER JOIN LeaseTerm ON Lease.Lease_ID = LeaseTerm.Lease_ID) INNER JOIN Tenant ON Lease.Tenant_ID = Tenant.Tenant_ID WHERE (((LeaseTerm.LeaseTerm_ID)=[Forms]![CreateInvs]![LeaseTerm_ID]));"
DoCmd.RunSQL StrQuery

InvDate = DateAdd(IntervalType, IntervalMonth, TermStartDate)
IntervalMonth = IntervalMonth + 1

Msg = "New date: " & InvDate & " " & Invoice_DateCreated & " Inv Record" & Invoice_ID
MsgBox Msg

Next InvNo

DoCmd.Echo True
DoCmd.SetWarnings True
MsgBox ("Invoices have been created for the Lease Term")
End Sub

**pbaldy** · 06-01-2017, 03:20 PM

You goofed part and I goofed part.

Try:

StrQuery = "INSERT INTO Invoices ( Invoice_TenantID, Invoice_LeaseID, Invoice_BaseRent, Invoice_AdditionalRent, Invoice_DateCreated, Invoice_Date )SELECT Tenant.Tenant_ID, LeaseTerm.Lease_ID, LeaseTerm.TermBaseRent, LeaseTerm.TermAdditionalRent, date(), #" & DateAdd('m',IntervalMonth,[TermStartDate]) & "# AS InvDate FROM (Lease INNER JOIN LeaseTerm ON Lease.Lease_ID = LeaseTerm.Lease_ID) INNER JOIN Tenant ON Lease.Tenant_ID = Tenant.Tenant_ID WHERE (((LeaseTerm.LeaseTerm_ID)=[Forms]![CreateInvs]![LeaseTerm_ID]));"

**Luisa** · 06-01-2017, 03:23 PM

Thank you for the suggestion, but I tried the insert query with: =[Forms]![CreateInvs]![LeaseTerm_ID])) leaving the DateAdd function out and it works. I just need to include the DateAdd function and keep adding 1 to the month for each loop.

**Luisa** · 06-01-2017, 03:39 PM

That worked... THANK YOU SO MUCH

!!! But I think something is wrong with my date format. The date format is Medium Date dd-MMM-YY. When I ran the Insert Query, it adds 1 to the DAY, not the MONTH? Should I change the date format on the table?

**pbaldy** · 06-01-2017, 04:03 PM

Happy to help! If you meant me.

What you have should add a month, just tested:

?dateadd("m",1,date())
7/1/2017

It is a date/time field, right?

**pbaldy** · 06-01-2017, 04:04 PM

Actually you may need this:

http://allenbrowne.com/ser-36.html#Format

**Luisa** · 06-01-2017, 04:24 PM

Yes Paul, I did mean you. I checked the Date field and it is a Date/Time field format Medium Date field. I tried changing the format to a Short Date, but it only adds 1 to the Day, not the Month?

**pbaldy** · 06-01-2017, 04:28 PM

Check that link I posted. You need to format the date in code to US format.

**Micron** · 06-01-2017, 06:35 PM

Luisa is a Cannuk (like me) and probably has English (Canada) in Regional settings. I can vouch that the function works regardless of which date format is used, but the issue may be incorrect interpretation of the results. If you add 1 month to 01/06/2017 (when settings are dd/mm/yyyy) you'll get 01/07/2017. If you add 1 month to today but the settings are mm/dd/yyyy the result will be 07/01/2017. It can simply be a matter of knowing the date format of your system so that you interpret the calculated date correctly. It's more obvious if the day portion is greater than 12.

**Luisa** · 06-01-2017, 06:53 PM

You are correct. My date fields are set as Medium Dates with format of dd-mmm-yyyy. I tried to change them to Short Dates with format of dd/mm/yyy and Long Dates with format of mmmm-dd-yy. Nothing seems to make a difference. It only adds one to the Date (eg. June 1, 2017, June 2, 2017, June 3, 2017 instead of June 1, 2017, May 1, 2017 and July 1, 2017).

I have the fields set up on the table as Medium Dates with dd-mmm-yyyy and I declared the InvDate As Date. Any suggestions? I appreciate that I got this far, but I can't seem to figure out why this doesn't work.

**Luisa** · 06-02-2017, 07:24 AM

Hi Paul,

I looked at the link and changed the INVDate format (see below). My System Date is mmmm-dd-yy or June-02-17. The Tables have date fields as Medium Dates of dd-mmm-yy or 02-Jun-17. No matter if I use Short Date or Long Date, the DateAdd function I use below is adding 1 to the Day not the Month. Sorry to keep bothering you with this.

Any suggestions would be greatly appreciated,
Luisa

Private Sub Command35_Click()

Dim InvNo As Long
InvNo = 1

Dim varDate As Variant
InvDate = Format$(varDate, "#mm\/dd\/yyyy\#")
InvDate = TermStartDate ' This is the first date invoices are created for

Dim IntervalType As String
IntervalType = "m" ' "m" specifies months as interval.

Dim IntervalMonth As Integer ' This is the interval to be added from the TermStartDate
IntervalMonth = 1

For InvNo = 1 To InvReq
Dim StrQuery As String
DoCmd.Echo False
DoCmd.SetWarnings False

StrQuery = "INSERT INTO Invoices ( Invoice_TenantID, Invoice_LeaseID, Invoice_BaseRent, Invoice_AdditionalRent, Invoice_DateCreated, Invoice_Date )SELECT Tenant.Tenant_ID, LeaseTerm.Lease_ID, LeaseTerm.TermBaseRent, LeaseTerm.TermAdditionalRent, date(), #" & DateAdd("m", IntervalMonth, [TermStartDate]) & "# AS InvDate FROM (Lease INNER JOIN LeaseTerm ON Lease.Lease_ID = LeaseTerm.Lease_ID) INNER JOIN Tenant ON Lease.Tenant_ID = Tenant.Tenant_ID WHERE (((LeaseTerm.LeaseTerm_ID)=[Forms]![CreateInvs]![LeaseTerm_ID]));"
DoCmd.RunSQL StrQuery

InvDate = DateAdd(IntervalType, IntervalMonth, TermStartDate)
IntervalMonth = IntervalMonth + 1

Msg = "New date: " & InvDate
MsgBox Msg

Next InvNo

DoCmd.Echo True
DoCmd.SetWarnings True
MsgBox ("Invoices have been created for the Lease Term")
End Sub

How do I pass a value to an insert query

Thread Tools

How do I pass a value to an insert query

DateAdd function

DateAdd function and Date Format

Similar Threads

Pass a variable to a Pass Through Query

Pass VBA variable to Public Variable and pass to Access 2007 Append Query attached to a form.

Pass a parameter to a pass through query using a form

INSERT query: insert new data only

Insert Pass-Through Results into Access Table with VB

Posting Permissions