Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Access VBA Error

Posted on 2013-01-15
Medium Priority
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 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

721 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