Solved

Sql: Too Few Parameters expected 1

Posted on 2016-11-15
8
41 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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
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)

 

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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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…

840 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