Solved

Sql: Too Few Parameters expected 1

Posted on 2016-11-15
8
35 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
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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 125 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 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 49

Accepted Solution

by:
Gustav Brock earned 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now