too few parameters, expected 1

Question

I have a small issue.....

DDA3 = "SELECT DDAs.[QR Code], DDAs.[Drug Name and Strength],DDAs.[Dispensing Date], DDAs.[Amount Prescribed], DDAs.[Prescription Date], DDAs.[Record Number], DDAs.[NOT DISPENSED] " _
& "FROM DDAs " _
& "WHERE ([DDAs].[QR Code] = [QR Code] AND [DDAs].[Dispensing Date] = [Date 2] AND [DDAs].[NOT DISPENSED] = No) " _

The above gives me the error

When i replace in the WHERE section [Date 2] with Date() (to use today's date instead of the date in the control) it works correctly....

I need to use [Date 2] I tried renaming the control constantly same error, tried removing [QR Code] to statement has only one control still did not work...

(Full code is below:

'On Error Resume Next

[Date 2] = Date

Dim rs As DAO.Recordset
Dim strOutput As String
Dim i As Integer
'Dim Date2 As Date

DDA3 = "SELECT DDAs.[QR Code], DDAs.[Drug Name and Strength],DDAs.[Dispensing Date], DDAs.[Amount Prescribed], DDAs.[Prescription Date], DDAs.[Record Number], DDAs.[NOT DISPENSED] " _
& "FROM DDAs " _
& "WHERE ([DDAs].[QR Code] = [QR Code] AND [DDAs].[Dispensing Date] = [Date 2] AND [DDAs].[NOT DISPENSED] = No) " _

'OR ([DDAs].[QR Code]=[QR Code] AND [DDAs].[Dispensing Date]= [DateMod] AND [DDAs].[NOT DISPENSED]= No) " _

Set rs = CurrentDb.OpenRecordset(DDA3)

With rs
'test for empty recordset
If Not .EOF And Not .BOF Then
    .MoveLast
    .MoveFirst
Dim cdg As String
cdg = UCase([Forms]![POYC].[txtEnterState6])

    For i = 0 To (.RecordCount - 1)
        'test for last record
        If i = (.RecordCount - 1) Then
            'last record
            strOutput = strOutput + ![Amount Prescribed] + " " + ![Drug Name and Strength]
        Else
            'all other records
            strOutput = strOutput + ![Amount Prescribed] + " " + ![Drug Name and Strength] + vbCrLf
        End If

        .MoveNext
    Next
End If
.Close
End With
'execute messagebox
MsgBox "The following DDA prescriptions for " & [cdg] & " have been dispensed:" & vbCrLf & vbCrLf & UCase([strOutput]), vbInformation + vbOKOnly, "DDA records!"
Set rs = Nothing

I have some code which I intentionally told access to ignore for now until i find the solution.... i plan to add the or part to the where statement so It runs the query either by Date() or by [Date 2]

Any Ideas?

DDA3 = "SELECT DDAs.[QR Code], DDAs.[Drug Name and Strength],DDAs.[Dispensing Date], DDAs.[Amount Prescribed], DDAs.[Prescription Date], DDAs.[Record Number], DDAs.[NOT DISPENSED] " _ & "FROM DDAs " _ & "WHERE ([DDAs].[QR Code] = [QR Code] AND [DDAs].[Dispensing Date] = [Date 2] AND [DDAs].[NOT DISPENSED] = 'No') " _ Still did not work.... When i change [Date 2] (a control on my form) to plain Date() the code works fine... — Zurrieq
adding the ' caused caused a type mismatch when i changed [Date 2] to Date() probably since that field is a yes/no tickbox in original table — Zurrieq
Does this answer your question? Problem with my update statement --getting error 3144. Where is [QR Code] parameter coming from? Suggest using 0 or False instead of No. — June7
Also review stackoverflow.com/questions/62582374/… and stackoverflow.com/questions/60749653/… — June7

Fabricio Fonseca Fabricio Fonseca · Accepted Answer · 2020-08-07T20:47:55

try with # before and after the date

DDA3 = "SELECT DDAs.[QR Code], DDAs.[Drug Name and Strength],DDAs.[Dispensing Date], DDAs.[Amount Prescribed], DDAs.[Prescription Date], DDAs.[Record Number], DDAs.[NOT DISPENSED] " _
& "FROM DDAs " _
& "WHERE ([DDAs].[QR Code] = [QR Code] AND [DDAs].[Dispensing Date] = #" & [Date 2] & "# AND [DDAs].[NOT DISPENSED] = No) " _

too few parameters, expected 1

1 Answers