VBA SQL Select Statement Not Working (1 Viewer)

[davidb88]

Hi All -

I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review. Can someone help me with this condition or point out where I am going wrong either with the logic or with syntax?

Thank you!

    strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _
            & "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;"
    objRs.Open strSQL, objConn, adLockReadOnly

 

[vbaInet]

Do you have access to the Access database?

 

[davidb88]

Yes I have Access to the database. Earlier in the module I am opening the connection to the database with ADO. The headers of the table pull in, however, no data comes through so I assume something is wrong with the query logic.

 

[plog]

Instead of posting the code that produces the SQL, find out what SQL is actually being produced.

Once you have that, go to your database, paste it into a query and see what happens.

 

[vbaInet]

So create a new query, open it in SQL view, paste your SQL statement in there and test it.

Obviously you won't be able to use variable names in the query so just type in the value.

 

[davidb88]

Thank you for your suggestions. When I paste my SQL statement in Access nothing is returned, meaning I have something wrong with my criteria. Am I adding days correctly in the SQL statement? I've never added days to a date before so I wasn't positive how to do it.

Thanks again.

 

[vbaInet]

Just to be sure, if you remove the criteria it returns data?

Copy and paste on here what you get from the SQL view with the criteria.

 

[davidb88]

Yes, if the criteria is removed, it returns data. The following query I created in Access and it pulls in the desired records. However, when I paste it into my VBA code in Excel it does not return anything, including the data headers. What am I doing wrong?

SELECT tblsojrol_oc.[Today's Date], tblsojrol_oc.Status, tblsojrol_oc.[1st Review Date], tblsojrol_oc.[2nd Review Date], tblsojrol_oc.[3rd Review Date]
FROM tblsojrol_oc
WHERE (((tblsojrol_oc.Status)='Pending') AND ((tblsojrol_oc.[1st Review Date]) Is Not Null) AND ((tblsojrol_oc.[3rd Review Date]) Is Null) AND (([tblsojrol_oc].[1st Review Date]+14)<=#7/3/2014#));

 

[vbaInet]

The problem is with your date criteria.

Format it as "mm/dd/yyyy" using the Format() function.

 

[davidb88]

Which part of the date criteria is off? When I use the SQL query is VBA the date itself (i.e. 7/3/2014) is substituted with a variable "dt" so I think it should automatically be in the same format if it is just the standard Date function, right?

 

[vbaInet]

If the date "7/3/2014" is supposed to be today's date why don't you use the Date() function as you've already done elsewhere in your SQL statement?

 

[davidb88]

I do use the date function when running it in VBA, but it still does not work. Any ideas?

 

[vbaInet]

I'm not seeing that in your first post. Is that what the dt variable returns? Still use the Format() function to force it to format properly.

 

[davidb88]

That is the dt variable that I just assign earlier in the module. I will try the format function and post back on if it worked or not. Thank you for all of your help

 

[vbaInet]

Can I ask what's the purpose of using the variable and not the function?

 

[davidb88]

There really isn't any point. There are other queries in the module though where I am using today's date so I thought it would be easier to assign a variable to the function.

 

[vbaInet]

In that case enclose the Date() function inside your SQL string and remove the #. Don't concatenate.

 

[davidb88]

Thank you!

That seems to have fixed my problem!

 

[vbaInet]

Good to hear you got it working!