Access VBA Error

Posted on 2013-01-15
Last Modified: 2013-01-15
Hi all.

I get an error when my Access form gets to the following code. I know I have the date parameters portion wrong but I don't know how to get it to work. Any ideas?

Thank you in advance!

Set rsExcel = CurrentDb.OpenRecordset("SELECT [Division]+'-'+[CustomerNumber] AS Customer, InvHistoryHeader.InvoiceNumber, InvHistoryHeader.InvoiceDate,Invoice.TrackingID" & _
" FROM InvHistoryHeader INNER JOIN InvoiceTracking ON InvHistoryHeader.InvoiceNumber = InvoiceTracking.InvoiceNumber WHERE ((([Division]+'-'+[CustomerNumber])='" & Me.txtMASID & "') AND ((InvHistoryHeader.InvoiceDate)>= #" & Me.txtDate1 &"#  And " (InvHistoryHeader.InvoiceDate)<= #" & Me.txtDate2 &" # )) ORDER BY InvHistoryHeader.InvoiceDate")

Open in new window

Question by:printmedia
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
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 38780460
Try this:

Dim strSQL as string
strSQL = "SELECT [Division]+'-'+[CustomerNumber] AS Customer, InvHistoryHeader.InvoiceNumber, InvHistoryHeader.InvoiceDate,Invoice.TrackingID " _
    & " FROM InvHistoryHeader INNER JOIN InvoiceTracking ON InvHistoryHeader.InvoiceNumber = InvoiceTracking.InvoiceNumber " _
    & " WHERE [Division]+'-'+[CustomerNumber] ='" & Me.txtMASID & "' " _
    & " AND InvHistoryHeader.InvoiceDate)>= #" & Me.txtDate1 & "#  And " _
    & " InvHistoryHeader.InvoiceDate <= #" & Me.txtDate2 & "#  ORDER BY InvHistoryHeader.InvoiceDate"

Set rsExcel = CurrentDb.OpenRecordset(strSQL)

Open in new window


Author Comment

ID: 38780463
I've requested that this question be deleted for the following reason:

Solved it myself.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
print previewing the record showing on the form 4 51
Delete table fields 3 48
Balance after Repayment - 2 6 62
Close gap between sub forms on a main form 5 28
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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