Solved

Sql: Too Few Parameters expected 1

Posted on 2016-11-15
8
39 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

803 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