Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql: Too Few Parameters expected 1

Posted on 2016-11-15
8
Medium Priority
?
50 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 52

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

Industry Leaders: 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!

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 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