UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Code Error, Access 2010    
 
   
rizmin
post Feb 19 2018, 07:06 AM
Post#1



Posts: 148
Joined: 10-August 17



Hi UA Nation!!

Any one can tell me where is the error from below code? It is showing runtime error as shown in the picture

CODE
Dim SQL As String
Dim db As Database
Dim rs As DAO.Recordset
Dim strSearch As String

Set db = CurrentDb()

'strSearch = Me.txtReportSN.Value Format (#17/04/2004#, "Short Date")
Set rs = db.OpenRecordset("SELECT * FROM 2017 WHERE ([Production Date] = " & Format(txtDate, "Medium Date") & " and shift= " & cboShift & " And Plant#=" & cboPlant & " And [Production Code]=" & cboCode & ")")
If rs.EOF = False Then
Me.txtProductName.Value = rs("Product Description")
Me.txtQty.Value = rs("Produced Qty#")
txtQty.Value = rs("Units")
Else
MsgBox "No Records is Found for the Product Code: " & Me.cboCode, vbInformation, "No Data Found"
rs.Close
Set rs = Nothing
End If


Thank you all
Attached File(s)
Attached File  Capture.JPG ( 22.02K )Number of downloads: 0
 
Go to the top of the page
 
ranman256
post Feb 19 2018, 07:24 AM
Post#2



Posts: 875
Joined: 25-April 14



DELIMITERS!!
strings must be delimited with single quotes:
[PLANT]='" & cboPlant & "'"

dates must delimit with #:
WHERE ([Production Date] =#" & (txtDate) & "#"

If you use queries ,instead of SQL, it will tell you the errors.
This post has been edited by ranman256: Feb 19 2018, 07:25 AM
Go to the top of the page
 
BruceM
post Feb 19 2018, 07:41 AM
Post#3


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


Several things having to do with data types and date format.

Dates in criteria expessions are delimited with octothorpes (#), and must be either in format mm/dd/yyyy (or m/d/yy, etc.) or yyyy/mm/dd. Access may be able to sort out dd-mmm-yyyy if English is being used, but if somebody else has dd/mm/yyyy as the short date format it will not work in cases where Access could interpret it as mm/dd/yyyy. 19/02/2017 will most likely be understood correctly since there is no 19th month, and Access tries to find a way to make the date work, but it will interpret 10/02/2017 as Oct. 2 2017, even if Feb. 10 is intended.

Text values must be delimited with either single or double quotes. Single quotes work unless the text contains an apostrophe, which is common with last names, company names, etc.

The parentheses around the Where condition are not needed. They do no harm, but I tend not to add extra parentheses.

With that in mind:

"[Production Date] = " & Format(txtDate, "\#yyyy\/mm\/dd\#") & " and Shift= '" & cboShift & "' And Plant#='" & cboPlant & "' And [Production Code]='" & cboCode & "'"

In the Format function a backslash means that what follows is a literal character. You can do without it for / or - in a date, but as a general practice I tend to use them.

It is good practice to assign to variables SQL and other strings, so you can use Debug.Print to evaluate the SQL:

Dim strSQL As String

strSQL = "SELECT * FROM 2017 WHERE [Production Date] = " & Format(txtDate, "\#yyyy\/mm\/dd\#") & " and Shift= '" & cboShift & "' And Plant#='" & cboPlant & "' And [Production Code]='" & cboCode & "'"

Debug.Print strSQL

db.OpenRecordset(strSQL)

In the testing phase I don't try to do anything with the SQL until I have verified it, so I would comment out the OpenRecordset line. Run the code, then press Ctl + G to open the Immediate code window, where the strSQL line should appear. You can copy it and paste it into SQL view of a query to be sure it is producing the intended result. Remove the Debug.Print code, and uncomment db.OpenRecordset, when testing is complete. More information about debugging techniques here

Another point is to use only letters, numbers, and underscores for names. Things like Plant# should be avoided, to be sure (in this case) Access doesn't mistake it as a date delimiter. PlantNum would be better. If you are committed to using the name (it is already in use and is too difficult to change, or whatever) be sure to enclose the name in square brackets. Similarly, a hyphen could be mistaken as subtraction.

Is 2017 a table? If so, there is most likely a design problem. If it is a query it is a little less of a problem, but it would be better to have a general query to which criteria are applied rather than having a separate query for each year. Also, I believe best practice is not to start names with numbers, to be sure it isn't mistaken for a number.
Go to the top of the page
 
rizmin
post Feb 19 2018, 07:59 AM
Post#4



Posts: 148
Joined: 10-August 17



Hi BruceM & ranman

I have tried the following code, since my table 2017 date format is in dd-mm-yyyy format,

But its giving another runtime error lika showing to the code
CODE
Set rs = db.OpenRecordset(strSQL)


pullhair.gif


CODE
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT * FROM 2017 WHERE [Production Date] = #" & Format(txtDate, "Medium Date") & "# and [Shift]= '" & cboShift & "' And [Plant#]='" & cboPlant & "' And [Production Code]='" & cboCode & "'"

Debug.Print strSQL

Set rs = db.OpenRecordset(strSQL)

If rs.EOF = False Then
Me.txtProductName.Value = rs("Product Description")
Me.txtQty.Value = rs("Produced Qty#")
txtQty.Value = rs("Units")
Else
MsgBox "No Records is Found for the Product Code: " & Me.cboCode, vbInformation, "No Data Found"
rs.Close
Set rs = Nothing
End If


Thank you
Attached File(s)
Attached File  Capture.JPG ( 19.15K )Number of downloads: 0
 
Go to the top of the page
 
BruceM
post Feb 19 2018, 08:20 AM
Post#5


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


It would help to see the Debug.Print result, but one likely problem is that you are continuing to use Short Date rather than the suggested formatting.

The date format in the table is irrelevant. The date is stored as a number representing the number of days since Dec. 31 1899. The decimal portion of the date/time value is 0 (midnight) if the time is not specified, or the fraction of the day otherwise. Today is 43150. 6:00 AM today is 43150.25 (exactly one fourth of the day). This number is the same regardless of the field's format in the table. I don't format table fields at all, leaving such manipulations for queries or expressions.

Remember that named formats such as Short Date are specific to the individual computer.
Go to the top of the page
 
GroverParkGeorge
post Feb 19 2018, 08:25 AM
Post#6


UA Admin
Posts: 33,497
Joined: 20-June 02
From: Newcastle, WA


IF it were my project, I would NOT use the Format() function in this context. Format converts the result into a STRING. I.e. it is no longer a date, but a string of digits and slashes, which humans see as the same thing as a date, but the computer no longer sees as a date.

With that in mind, you can make your life a lot simpler by not bothering with the Format() function here. As long as the controls being referenced on the form contain valid dates, use the dates!

And, one observation on using single quotes vs double quotes. There's no reason to say that strings must be delimited with single quotes. That's one alternative. The other is to double up the quotes. Some people find the single quotes easier to read, but they normally would work the same in most cases.

Looking at your SQL string, a couple of things puzzle me. Do you really have a table or a query named "2017"?
Are the bound columns of the controls named "cboPlant" and "cboCode" really a strings? In other words, are the Plant# field and the "[Production Code]" field both strings? Not AutoNumber Primary Keys?


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
BruceM
post Feb 19 2018, 09:51 AM
Post#7


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


I have never met GroverParkGeorge, but I think of him as a friend through years of interacting with him and seeing him in action in this forum. With that stated, I have to disagree somewhat with my esteemed friend George on this point:

QUOTE
IF it were my project, I would NOT use the Format() function in this context. Format converts the result into a STRING. I.e. it is no longer a date, but a string of digits and slashes, which humans see as the same thing as a date, but the computer no longer sees as a date.

That is OK as long as you are certain all users are using the U.S. date format. The mm/dd/yyyy format is used in a few other locales too, but more common is dd/mm/yyyy. In that case, consider this expression:

"WHERE DateField = " & Date()

Today that would be:

WHERE DateField = 19/02/2018

Access will get it right since the only way to interpret that is Feb. 19 2017. However, ten days ago it would have been:

WHERE DateField = 09/02/2018

As far as Access is concerned that is Sep. 2 2018. Changing the regional settings will not change how Access evaluates 09/02/2018.

While it is true that Format produces a string, the date delimiters (#) tell Access that it is a date, and Access handles it as such.

If Date() is used as the criteria for a Date/Time field directly in a query, Access will get it right no matter the regional settings, from what I can tell. However, when a Date/Time value is concatenated into a string in VBA, the result will vary depending on the regional settings. The same applies when an unbound text box is used to supply a date parameter. I verified this in tests I just conducted that returned the results as described above.

If everybody is using a U.S. Short Date format, or if the text box is formatted dd-mmm-yyyy and English is the language being used, the date will be handled properly in the SQL string (although I find that the date delimiters are needed in any case).

The short answer is that it could work if all users are using U.S. date format, but unless the users' regional settings are locked down it may not be possible to ensure that.

The Debug.Print result offers the best chance, I think, to evaluate the SQL string.
Go to the top of the page
 
GroverParkGeorge
post Feb 19 2018, 10:26 AM
Post#8


UA Admin
Posts: 33,497
Joined: 20-June 02
From: Newcastle, WA


Thanks for the kind words, Bruce.

You are right, of course, about how Access will handle the values.

My point is that the first step here appears to be:

Convert a date to a string using the Format() function to return a specific DISPLAY for the date. A display that no human eye is actually going to see, by the way.

But the second step is:

Convert the resulting string back into a date by applying the date delimiter.

I don't see the point of two conversions in a VBA procedure. Why not use the original date itself in the parameter? As you pointed out, regional settings may impact how that has to be handled. Nonetheless, back-and-forth from date to string to date seems redundant to my way of thinking.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
BruceM
post Feb 19 2018, 10:35 AM
Post#9


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


George, you may be correct as long as the parameter value is a date/time value such as Date(), a date/time field, or a function such as DateAdd that returns a date serial number. For an unbound text box, though, I'm not sure the value is treated as a true date/time value, and thus the need to convert the value in the VBA string. At the same time, the text box value is already formatted, so I certainly agree that there is no reason to format it again.

The Debug.Print result would fill in some of the gaps. There is no good substitute for seeing the actual value with which Access is working.

Go to the top of the page
 
rizmin
post Feb 19 2018, 11:08 PM
Post#10



Posts: 148
Joined: 10-August 17



Hi BruceM & Geroge,

Good Day!

Yes its working well now, Thank you very much you both... notworthy.gif

I have used the code as following
CODE
Dim db As Database
Dim rs As DAO.Recordset
Dim strCriteria As String
Dim task As String
Dim strSQL As String

Set db = CurrentDb()
strSQL = "SELECT * FROM 2017 WHERE [Production Date] = " & Format(txtDate, "\#yyyy\/mm\/dd\#") & " and Shift= '" & cboShift & "' And [Plant #]='" & cboPlant & "' And [Product Code]='" & cboCode & "'"
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)

If rs.EOF = False Then
Me.txtProductName.Value = rs("Product Description")
Me.txtQty.Value = rs("Produced Qty#")
Me.txtUnit.Value = rs("Units")
Else
MsgBox "No Records is Found for the Product Code: " & Me.cboCode, vbInformation, "No Data Found"
rs.Close
Set rs = Nothing
End If

This post has been edited by rizmin: Feb 19 2018, 11:30 PM
Go to the top of the page
 
BruceM
post Feb 20 2018, 07:21 AM
Post#11


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


Glad to hear you got it working. George and I are glad to help. Good luck with the project.
Go to the top of the page
 
GroverParkGeorge
post Feb 20 2018, 09:25 AM
Post#12


UA Admin
Posts: 33,497
Joined: 20-June 02
From: Newcastle, WA


Thanks, Bruce.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2018 - 12:19 AM