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, Aug 07 '20 at 20:02
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, Aug 07 '20 at 20:06
Does this answer your question? [Problem with my update statement --getting error 3144](https://stackoverflow.com/questions/61622669/problem-with-my-update-statement-getting-error-3144). Where is [QR Code] parameter coming from? Suggest using 0 or False instead of No. — June7, Aug 07 '20 at 20:27
Also review https://stackoverflow.com/questions/62582374/syntax-missing-operator-error-saving-string-contents-to-variable-with-runsql-i/62582747#62582747 and https://stackoverflow.com/questions/60749653/append-query-in-vba-for-microsoft-access-not-taking-the-where-parameters/60749766#60749766 — June7, Aug 07 '20 at 20:32
[QR Code] is also a control in the same form (and a field in the DDAs table). This is not giving me issues... — Zurrieq, Aug 08 '20 at 04:25
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271)! Always refer to controls by form (either name `Forms("FormName")` or `Me` (Form where code resides) to avoid getting mixed up with fields of same name (what should also be avoided). You get rid of most code by just concating all rows with a CrLf and only remove the last unwanted one! — ComputerVersteher, Aug 09 '20 at 05:31

score 0 · Answer 1 · answered Aug 07 '20 at 20:47

0

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) " _

answered Aug 07 '20 at 20:47

Fabricio Fonseca

139
5

I will try this tomorrow when i am back on my pc. I also tried #7/8/2020# (random date) and that worked so will see when i add the # around the control. – Zurrieq Aug 08 '20 at 04:26
same error.... any more ideas? Tried the following Datex = "#" & Format([Date 2], "dd/mm/ yyyy") & "#" Datey = "#" & [Date 2] & "#" and used [DDAs].[Dispensing] = Datex and also tried with Datey still the same.... – Zurrieq Aug 08 '20 at 15:21
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] = #" & Format([Date 2], "mm/dd/ yyyy") & "#) AND [DDAs].[NOT DISPENSED] = No) OR ([DDAs].[QR Code] = [QR Code] AND [DDAs].[Dispensing Date]= Date() AND [DDAs].[NOT DISPENSED]= No) " _ This solved it and is the full working query!! – Zurrieq Aug 08 '20 at 15:37

too few parameters, expected 1

1 Answers1