?
Solved

Sql: Too Few Parameters expected 1

Posted on 2016-11-15
8
Medium Priority
?
49 Views
Last Modified: 2016-11-16
hello,

I have the below that I can't seem to get sorted out:

                        Dim Result As String
                        MyDate = Format(Date, "Short Date")
                        MyTime = Format(Time, "Short Time")
                        Result = Me.Key
                        mylookup = DLookup("Key", "tblHWWPass", "Key=" & Result & " And IsNull(DateOut)")
                        'Debug.Print mylookup
                        'Debug.Print Result
                        If mylookup > 0 Then
                            strSQL = "UPDATE tblHWWPass " _
                            & "SET TimeReturned =MyTime" _
                            & ", DateOut =#" & MyDate & "#" _
                            & " WHERE Key =" & Result _
                            & " And IsNull(DateOut)"
                        'Debug.Print strSQL
                        CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
Comment
Question by:Ernest Grogg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75
ID: 41888921
See if brackets around the field name DateOut fixes this

 strSQL = "UPDATE tblHWWPass " _
                            & "SET TimeReturned =MyTime" _
                            & ", [DateOut] =#" & MyDate & "#" _
                            & " WHERE Key =" & Result _
                            & " And IsNull([DateOut])"
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 41888924
Also ... what data type is & MyTime ?

If not a number:
if text
& "SET TimeReturned =" & Chr(34) & MyTime & Chr(34)  _

If DateTime

& "SET TimeReturned = " & "#" & MyTime & "#" _
0
 

Author Comment

by:Ernest Grogg
ID: 41888925
Just left the office will be back in the morning and let you know
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ernest Grogg
ID: 41888945
mytime is just that the current time...

looking to update the fields for the current date and time based on the key # and if the date returned  IsNull
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 41888953
you have to bracket Key  - [Key]

                        Dim Result As Long
                        MyDate = Format(Date, "Short Date")
                        MyTime = Format(Time, "Short Time")
                        Result = Me.[Key]
                        mylookup = DLookup("Key", "tblHWWPass", "[Key]=" & Result & " And IsNull(DateOut)")
 
                        If mylookup > 0 Then
                            strSQL = "UPDATE tblHWWPass " _
                            & "SET TimeReturned =MyTime" _
                            & ", DateOut =#" & MyDate & "#" _
                            & " WHERE [Key] =" & Result _
                            & " And IsNull(DateOut)"
                        
                        CurrentDb.Execute strSQL, dbFailOnError

Open in new window



If Key is String

                        Dim Result As String
                        MyDate = Format(Date, "Short Date")
                        MyTime = Format(Time, "Short Time")
                        Result = Me.[Key]
                        mylookup = DLookup("Key", "tblHWWPass", "[Key]='" & Result & "' And IsNull(DateOut)")
 
                        If mylookup > 0 Then
                            strSQL = "UPDATE tblHWWPass " _
                            & "SET TimeReturned =MyTime" _
                            & ", DateOut =#" & MyDate & "#" _
                            & " WHERE [Key] ='" & Result & "' _
                            & " And IsNull(DateOut)"
                        
                        CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
 
LVL 75
ID: 41888974
Then this part s/b:

& "SET TimeReturned = " & "#" & MyTime & "#" _
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 41889335
The errors are here where you don't concatenate the variable:

    "SET TimeReturned =MyTime"

and here:

    & " WHERE Key =" & Result _

However, you really don't need the DLookup to check. The Update will take a little time to find out nothing should be done, but so will DLookup.
Thus:
                        Dim Result As String
                        Dim strSQL As String
                        Dim MyDate As String
                        Dim MyTime As String

                        MyDate = Format(Date, "yyyy\/mm\/dd")
                        MyTime = Format(Time, "hh\:nn\:ss")
                        Result = Me!Key.Value

                        strSQL = "UPDATE tblHWWPass " _
                            & "SET TimeReturned = #" & MyTime & "#, " _
                            & "DateOut = #" & MyDate & "# " _
                            & "WHERE [Key] ='" & Result & "' "_
                            & "And [DateOut] Is Null"

                        CurrentDb.Execute strSQL, dbFailOnError

and if Key is not a string but an integer/long:

                        Dim Result As Long
                        ' snip
                            & "WHERE [Key] =" & Result & " "_

Open in new window

/gustav
1
 

Author Closing Comment

by:Ernest Grogg
ID: 41889526
Thanks to everyone.

Updated the sql and removed the DLookUp, since Gustav had a good thought and was right.  in the full scope of the procedure, it seemed to be doing the work twice.  Don't need that!

Thanks to everyone.  Awesome as always!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question