VBA Insert Into Query that uses Unbound Text Box

[RangerAlex]

I have the following code that is designed to insert the results of a query into a separate table, preparing them for export to Excel. This is done on a form which uses combo boxes. The filters are in the "WhereStatement." It works great until I attempt to include this part: Nz(Me.txtTargetDate.value,0) AS TargetDate. This is my attempt at targeting an unbound textbox that holds a date on the parent forum. It results in an error "Run-time error '3061': Too few parameters. Expected 1." I have tried other various ways to modify this line and only get other types of errors. What am I doing wrong here?

Option Compare Database
Option Explicit

Private WhereStatement As String
Private Sub btnExcelExport_Click()
Dim db As DAO.Database
Dim sSQL As String
Set db = CurrentDb
 
sSQL = "INSERT INTO tblExcelExport (ExcelExport_BackdaterID, ExcelExport_Project0IDfk, ExcelExport_Project1IDfk, ExcelExport_Project2IDfk, ExcelExport_Task, ExcelExport_StatDate, " _
& "ExcelExport_DueDate, ExcelExport_SnoozeUntil, ExcelExport_DaysAhead_ReminderTime, ExcelExport_OwnerIDfk, ExcelExport_IncludeInExport, ExcelExport_DueDate_DaysPrior,ExcelExport_EventDate) " _
& "SELECT tblBackdater.BackdaterID, tblBackdater.Backdater_Project0IDfk, tblBackdater.Backdater_Project1IDfk, tblBackdater.Backdater_Project2IDfk, tblBackdater.Backdater_Task, " _
& "tblBackdater.DaysPriorIDfk_StatDate, tblBackdater.DaysPriorIDfk_DueDate, tblBackdater.DaysPriorIDfk_SnoozeUntil, tblBackdater.Backdater_DaysAhead_ReminderTime, tblBackdater.Backdater_OwnerIDfk, " _
& "tblBackdater.Backdater_IncludeInExport, lkqDaysPrior.DaysPrior_Number, Nz(Me.txtTargetDate.value,0) AS TargetDate FROM qryBackdater " _
& WhereStatement & "ORDER BY lkqDaysPrior.DaysPrior_Number ASC;"

 db.Execute sSQL, dbFailOnError

[orange]

What exactly is wherestatement? Where is is defined?

[Welshgasman]

You need to CONCATENATE the control value

& "tblBackdater.Backdater_IncludeInExport, lkqDaysPrior.DaysPrior_Number, " &  Nz(Me.txtTargetDate,0) " AS TargetDate FROM qryBackdater " _

However Dates normally are surrounded by # as you would have #mm/dd/yyyy# or #yyyy-mm-dd#

Edit:Tip: Debug.Print the sql statement to see what gets produced, not what you think gets produced.

[kd2017]

What about getting rid of the NZ function? I think you should get a data type mismatch if you try to insert select a 0 into a date field.

[Welshgasman]

I would use CDate(2) as a default, as at least that will supply a date 01/01/1900 ?

[RangerAlex]

Well I think you all in part are correct. Welshgasman, I concatenated the string and now it is printing a date in the new table column; however, it is printing the wrong date. If I use debug.print in VBA, it prints the correct date in the immediate window if I . I've tried adding in CDate(), removing the Nz(), Removing the As TargetDate portion, verified the data type in the new table is a date string, I even tried making it a different data type. All with no luck. It still is printing the date 12/30/1899 into the new table regardless of what I do. All other fields are inserted correctly. Any more ideas?

[Welshgasman]

12/30/1899 is the date for a numeric value of 1?
Which is why I suggested the value 2?
You supply a value for whatever date you want. I would supply the date in date format, so as to be easy to ascertain what the code is doing?

[davegri]

How about this?

 Nz(Me.txtTargetDate.value,0) AS ExcelExport_EventDate FROM qryBackdater " _

[RangerAlex]

Well I guess I learned something today. Even though I declared the variable as a Date everywhere, I only got it to work correctly when I added the # symbols as you mentioned before. I just didn't use the correct syntax to do it originally.

Final working relevant code:

& "#" & TargetDate & "#" &

FYI I went back and declared the TargetDate separatley:

Dim TargetDate As Date

TargetDate = CDate(Me.txtTargetDate.Value)

[Welshgasman]

I have never declared or used a date as just a numeric.? Whilst I know they are stored as such, I have always used dates in their visual format and as in the UK, know you need to format as mm/dd/yyyy or yyyy-mm-dd

so NZ(Me.txtTargetDate,#01/01/1900#) works just as well ?