Data Type Mismatch

[Jake]

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 RsttmpSaleDetai ls As DAO.Recordset
Dim RstDailySalesRe port As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblSalesDetails WHERE SalesID = " & txtSalesID
Set RstSaleDetails = CurrentDb.OpenR ecordset(strSQL , dbOpenDynaset)
Set RsttmpSaleDetai ls = CurrentDb.OpenR ecordset("tmptb lSaleDetails")

RsttmpSaleDetai ls.MoveFirst

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

RsttmpSaleDetai ls.MoveNext

Loop

[Randy Harris]

"Jake" <j.***********@ mchsi.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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 RsttmpSaleDetai ls As DAO.Recordset
Dim RstDailySalesRe port As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblSalesDetails WHERE SalesID = " & txtSalesID
Set RstSaleDetails = CurrentDb.OpenR ecordset(strSQL , dbOpenDynaset)
Set RsttmpSaleDetai ls = CurrentDb.OpenR ecordset("tmptb lSaleDetails")

RsttmpSaleDetai ls.MoveFirst

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

RsttmpSaleDetai ls.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 = '" & RsttmpSaleDetai ls!UPC & "'"

HTH,
Randy

[Access]

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

[oliverte]

Change the following string as follows:

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

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