MakeTable with SQL in VBA

[The_Vicar75]

Hi,

I need to make a table, in VBA using SQL, with 2 parameters (variables). (a startdate & enddate).

I made some SQL statement, it seems to do what it needs to do (table is made), but the table that is made contains no records. The 2 variables are filled with dates, but...

someone got an answer?

thanx in advance,
The_Vicar75
------------
SQL:
Private Function MaakTabel_Open_tss_2Data()
Dim SQLstring As String
DoCmd.SetWarnings False

SQLstring = "SELECT tblOverview.NcNumber, tblOverview.IssueDate, tblOverview.NonConformity, tblOverview.lkpStatus, tblOverview.ClosingDate, tblOverview.Feedback INTO tbl_Afsluiting_020_OPEN " & _
"FROM tblOverview " & _
"WHERE (((tblOverview.IssueDate) Between " & Datum01 & " AND " & Datum02 & ") AND ((tblOverview.lkpStatus)='open' Or (tblOverview.lkpStatus)='follow-up')) " & _
"ORDER BY tblOverview.IssueDate;"

DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True


End Function

 

[jzwp22]

The dates have to be enclosed in # signs

"WHERE (((tblOverview.IssueDate) Between #" & Datum01 & "# AND #" & Datum02 & "#) AND ((tblOverview.lkpStatus)='open' Or (tblOverview.lkpStatus)='follow-up')) " & _

I usually put a debug.print after the SQL to check the text in the immediate window for any syntax issues

debug.print SQLstring

 

[The_Vicar75]

jzwp22,

You are my new God!

THX!
V.

 

[jzwp22]

Glad to help; good luck on the project.

 

[The_Vicar75]

additional question on this matter:

want to add 2 fields, this works fine, but want to fill up these fields with begin & end-date, every record the same.

I did this, but again, no records in the table that is made.

thx,
J.
SQLstring = "SELECT tblOverview.NcNumber, tblOverview.IssueDate, tblOverview.NonConformity, tblOverview.lkpStatus, tblOverview.ClosingDate, tblOverview.Feedback, Begindatum, Einddatum INTO tbl_Afsluiting_020_OPEN " & _
"FROM tblOverview " & _
"WHERE (((tblOverview.IssueDate) Between #" & Datum01 & "# AND #" & Datum02 & "#) AND ((tblOverview.lkpStatus)='open' Or (tblOverview.lkpStatus)='follow-up')) AND ([Begindatum] = #" & Datum01 & "#) AND ([Einddatum] = #" & Datum02 & "#) " & _
"ORDER BY tblOverview.IssueDate;"

 

[jzwp22]

I think you have to do this differently; you are probably not selecting the same records because of the additional AND you added to the WHERE clause which will restrict your records further

WHERE (((tblOverview.IssueDate) Between #" & Datum01 & "# AND #" & Datum02 & "#) AND ((tblOverview.lkpStatus)='open' Or (tblOverview.lkpStatus)='follow-up')) AND ([Begindatum] = #" & Datum01 & "#) AND ([Einddatum] = #" & Datum02 & "#) " & _
"ORDER BY tblOverview.IssueDate;"

You would add a couple expressions to the SELECT clause to populate the dates. Something along this line (not sure if I have the syntax exactly correct)


SQLstring = "SELECT tblOverview.NcNumber, tblOverview.IssueDate, tblOverview.NonConformity, tblOverview.lkpStatus, tblOverview.ClosingDate, tblOverview.Feedback, #" & Datum01 & "# as Begindatum, #" & Datum02 & "# as Einddatum INTO tbl_Afsluiting_020_OPEN " & _
"FROM tblOverview " & _
"WHERE (((tblOverview.IssueDate) Between #" & Datum01 & "# AND #" & Datum02 & "#) AND ((tblOverview.lkpStatus)='open' Or (tblOverview.lkpStatus)='follow-up')) " & _
"ORDER BY tblOverview.IssueDate;"

 

[The_Vicar75]

runs like a train...

thx again!

bye,
V.

 

[The_Vicar75]

jzwp22,

Could I trouble you again? ;-)

You might have a nice solution to this: what i'm trying to do with this project is counting rows, actually. But I need to put in diff parameters (startdate, enddate, etc...) when I push a commandbutton.

So, what is the best way to count rows? What I did, but I'm not thát experienced, was make a table with the SQL-statement below, then count the rows (open recordset, movelast & count).

Is there a better, faster way to do so?

Thx again.

Greetings,
V.