Select query based on another table

[davidb88]

Hi -

I am trying to insert a select query into my database where the date of the records in my tbl1 are equal to the date stored in tblmodeldate. Usually I know you would do an inner join on the two fields and it would work properly. The only issue with that is that I am not only looking to bring in the records for which the date matches the date stored in tblmodeldate but also for records from the prior month. Therefore I don't think an inner join would work, correct? Is there a way to do a select query that has criteria that is based on another table without using a join so I can have two criteria? Any suggestions would be appreciated!

Thanks!

 

[davidb88]

Just as a quick follow-up I did a couple other searches and found a good approach to accomplish this would may be through assigning the current month and the previous months as variables. I have come up with the following code, but still cannot get it to work properly. I have verified that the variables are getting assigned properly through using the debug.print function, but still nothing is coming through on the table that it is creating. When I take out the date criteria, the table gets populated so I know that the date criteria is the issue with this set of code, just can't seem to figure out what about it is incorrect. Any suggestions?

Sub CalculatePD()
Dim modeldate As Date
Dim priormodeldate As Date
Dim strSQL As String
DoCmd.setwarnings False


'Assign modeldate variable to the stored value in tblmodeldate
modeldate = DLookup("modeldate", "tblmodeldate")
'Assign priormodeldate to the month prior to stored value in tblmodeldate
priormodeldate = DateSerial(Year(modeldate), Month(modeldate), 0)


strSQL = "SELECT tblLHFIedit.Month, tblLHFIedit.[Loan Type], tblLHFIedit.[FICO Band] INTO tbl1stfico1 " _
        & "FROM tblLHFIedit " _
        & "WHERE (((tblLHFIedit.[Month])= " & modeldate & ") OR ((tblLHFIedit.[Month])= " & priormodeldate & ") " _
        & "AND ((tblLHFIedit.[FICO Band])='1') AND ((tblLHFIedit.[Loan Type])='1st Mtg'));"
DoCmd.RunSQL (strSQL)

 

[davidb88]

I answered my own question. I was missing #'s. For anyone in the future that is experiencing a similar issue, this is the final code that works for me:

Sub CalculatePD()
Dim modeldate As Date
Dim priormodeldate As Date
Dim strSQL As String
DoCmd.setwarnings False


'Assign modeldate variable to the stored value in tblmodeldate
modeldate = DLookup("modeldate", "tblmodeldate")
'Assign priormodeldate to the month prior to stored value in tblmodeldate
priormodeldate = DateSerial(Year(modeldate), Month(modeldate), 0)


strSQL = "SELECT tblLHFIedit.Month, tblLHFIedit.[Loan Type], tblLHFIedit.[FICO Band] INTO tbl1stfico1 " _
        & "FROM tblLHFIedit " _
        & "WHERE (((tblLHFIedit.[Month])= #" & modeldate & "#) OR ((tblLHFIedit.[Month])= #" & priormodeldate & "#) " _
        & "AND ((tblLHFIedit.[FICO Band])='1') AND ((tblLHFIedit.[Loan Type])='1st Mtg'));"
DoCmd.RunSQL (strSQL)