By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Data Type Mismatch

P: n/a
I am currently trying to create my own Point Of Sale software for my
retail store. I wrote the program with the UPC field as Long integer.
When I started to add the products by UPC code, I got a data mismatch
error. I realized UPC codes are 12 digits, sometimes more and
sometimes less. This falls out of the Long integer field type. I then
changed my type on all UPC fields in all of the tables containing this
field to a text data type. This would allow me to enter the UPC codes
in; however, after doing this much of my code gave me data type
mismatch errors. I get this error when using the Find method to update
my inventory table to reflect the changes made to the inventory from
the sale transaction. I also get this error when I try using a SQL in
my code to select from the Inventory table the record that matches the
UPC of the sale. Does anybody have any suggestions? Here is some of
the code I'm having problems with:

Dim RstSaleDetails As DAO.Recordset
Dim RsttmpSaleDetails As DAO.Recordset
Dim RstDailySalesReport As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblSalesDetails WHERE SalesID = " & txtSalesID
Set RstSaleDetails = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set RsttmpSaleDetails = CurrentDb.OpenRecordset("tmptblSaleDetails")

RsttmpSaleDetails.MoveFirst

Do Until RsttmpSaleDetails.EOF
RstSaleDetails.FindFirst "UPC = " & RsttmpSaleDetails!UPC
If RstSaleDetails.NoMatch = True Then
RstSaleDetails.AddNew
RstSaleDetails!SalesID = txtSalesID
RstSaleDetails!UPC = RsttmpSaleDetails!UPC
RstSaleDetails!QtySold = RsttmpSaleDetails!QtySold
RstSaleDetails.Update
Else
RstSaleDetails.Edit
RstSaleDetails!QtySold = RstSaleDetails!QtySold +
RsttmpSaleDetails!QtySold
RstSaleDetails.Update
End If

RsttmpSaleDetails.MoveNext

Loop

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Jake" <j.***********@mchsi.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
I am currently trying to create my own Point Of Sale software for my
retail store. I wrote the program with the UPC field as Long integer.
When I started to add the products by UPC code, I got a data mismatch
error. I realized UPC codes are 12 digits, sometimes more and
sometimes less. This falls out of the Long integer field type. I then
changed my type on all UPC fields in all of the tables containing this
field to a text data type. This would allow me to enter the UPC codes
in; however, after doing this much of my code gave me data type
mismatch errors. I get this error when using the Find method to update
my inventory table to reflect the changes made to the inventory from
the sale transaction. I also get this error when I try using a SQL in
my code to select from the Inventory table the record that matches the
UPC of the sale. Does anybody have any suggestions? Here is some of
the code I'm having problems with:

Dim RstSaleDetails As DAO.Recordset
Dim RsttmpSaleDetails As DAO.Recordset
Dim RstDailySalesReport As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblSalesDetails WHERE SalesID = " & txtSalesID
Set RstSaleDetails = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set RsttmpSaleDetails = CurrentDb.OpenRecordset("tmptblSaleDetails")

RsttmpSaleDetails.MoveFirst

Do Until RsttmpSaleDetails.EOF
RstSaleDetails.FindFirst "UPC = " & RsttmpSaleDetails!UPC
If RstSaleDetails.NoMatch = True Then
RstSaleDetails.AddNew
RstSaleDetails!SalesID = txtSalesID
RstSaleDetails!UPC = RsttmpSaleDetails!UPC
RstSaleDetails!QtySold = RsttmpSaleDetails!QtySold
RstSaleDetails.Update
Else
RstSaleDetails.Edit
RstSaleDetails!QtySold = RstSaleDetails!QtySold +
RsttmpSaleDetails!QtySold
RstSaleDetails.Update
End If

RsttmpSaleDetails.MoveNext

Loop


Since UPC is now a text field, when used in a WHERE clause, or Find, or
DLookup, etc you'll need to surround the argument with quotes.
For example, you could use"
RstSaleDetails.FindFirst "UPC = '" & RsttmpSaleDetails!UPC & "'"

HTH,
Randy
Nov 13 '05 #2

P: n/a
My thoughts on this are, if I am not going to sort or use the data in
calculations then I store it as text.

Nov 13 '05 #3

P: n/a
Change the following string as follows:

from: strSQL = "SELECT * FROM tblSalesDetails WHERE SalesID = " &
txtSalesID

to: strSQL = "SELECT * FROM tblSalesDetails WHERE SalesID = '" &
txtSalesID & "'"

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.