Text vs Numeric error

[Supe]

I copied over a form that someone else had created for a different database
for a different vendor. When I try and run the form I get a Data Type
Mismatch In Criteria Type error. I noticed that the UPCCase field's Date
Type is a Text on the Master Table in the original database and it is Numeric
in the Master Table on the database I copied it to. I tried changing the
data type to Text in the table, but I get a "There isn't enough disc space or
memory" error.


I don't have much coding experience, but know that numeric and text has to
have certain quotes around them. I tried figuting out myself with no luck.
The code below is what the debug brings me too. Can someone look at this and
let me know what need to be change for this numeric UPCCase field to work.




DoCmd.RunSQL "INSERT INTO [VOID - Concantenated Master List] ( CustNbr,
Store, City, State, UPCCase, Expr1, SVItemCd, SVDescription, [Beg Date], [End
Date] ) SELECT DISTINCT [Heinz Master].CustNbr, [All in one store
reference].Store, [Heinz Master].City, [Heinz Master].State, [Heinz
Master].UPCCase, [CustNbr] & [UPCCase] AS Expr1, [Heinz Master].SVItemCd,
[Heinz Master].SVDescription, [Week Numbers].[Beg Date], [Week Numbers].[End
Date] " & _
"FROM ([All in one store reference] LEFT JOIN [Heinz Master] ON [All
in one store reference].[Store Num] = [Heinz Master].CustNbr) LEFT JOIN [Week
Numbers] ON [Heinz Master].[Invoice Week] = [Week Numbers].[Wk#] WHERE
([Heinz Master].UPCCase IN (" & strUPCs & ")) AND ([Heinz Master].[Invoice
Week] >= '" & begn & "' And [Heinz Master].[Invoice Week] <= '" & endn & "')
GROUP BY [Heinz Master].CustNbr, [All in one store reference].Store, [Heinz
Master].City, [Heinz Master].State, [Heinz Master].UPCCase, [CustNbr] &
[UPCCase], [Heinz Master].SVItemCd, [Heinz Master].SVDescription, [Week
Numbers].[Beg Date], [Week Numbers].[End Date];"

 

[John W. Vinson]

I tried changing the
data type to Text in the table, but I get a "There isn't enough disc space or
memory" error.

A solution to this error is to create a new, empty table with the desired
datatypes, and run an *append* query from your current table into the new
table. I'd actually suggest using a Date/Time datatype for the date field,
rather than either numeric or text. You don't say what the field contains, but
you'll need some sort of expression to convert it to a date; if it contains
(say) 20080430 for today's date, use a calculated field

DateSerial([datefield] \ 10000, [datefield] MOD 10000 \ 100, [datefield] MOD
100)

and append it to a Date/Time field in your new table.

I don't have much coding experience, but know that numeric and text has to
have certain quotes around them. I tried figuting out myself with no luck.
The code below is what the debug brings me too. Can someone look at this and
let me know what need to be change for this numeric UPCCase field to work.

Number fields should not have any quotemarks around them. The field UPCCase in
your query doesn't HAVE any quotes, so it shouldn't be causing the problem!
How is the VBA variable strUPCs defined? What's the datatype of [Invoice
Week]? That does have quotes and probably shouldn't:

DoCmd.RunSQL "INSERT INTO [VOID - Concantenated Master List] ( CustNbr,
Store, City, State, UPCCase, Expr1, SVItemCd, SVDescription, [Beg Date], [End
Date] ) SELECT DISTINCT [Heinz Master].CustNbr, [All in one store
reference].Store, [Heinz Master].City, [Heinz Master].State, [Heinz
Master].UPCCase, [CustNbr] & [UPCCase] AS Expr1, [Heinz Master].SVItemCd,
[Heinz Master].SVDescription, [Week Numbers].[Beg Date], [Week Numbers].[End
Date] " & _
"FROM ([All in one store reference] LEFT JOIN [Heinz Master] ON [All
in one store reference].[Store Num] = [Heinz Master].CustNbr) LEFT JOIN [Week
Numbers] ON [Heinz Master].[Invoice Week] = [Week Numbers].[Wk#] WHERE
([Heinz Master].UPCCase IN (" & strUPCs & ")) AND ([Heinz Master].[Invoice
Week] >= " & begn & " And [Heinz Master].[Invoice Week] <= " & endn & ")
GROUP BY [Heinz Master].CustNbr, [All in one store reference].Store, [Heinz
Master].City, [Heinz Master].State, [Heinz Master].UPCCase, [CustNbr] &
[UPCCase], [Heinz Master].SVItemCd, [Heinz Master].SVDescription, [Week
Numbers].[Beg Date], [Week Numbers].[End Date];"

 

[Supe]

I tried to append to a new database, but get an Invalid Argument error. The
original table has 5,761,555 lines of data in it. Is the error caused by the
size of this table?

I tried changing the
data type to Text in the table, but I get a "There isn't enough disc space or
memory" error.

A solution to this error is to create a new, empty table with the desired
datatypes, and run an *append* query from your current table into the new
table. I'd actually suggest using a Date/Time datatype for the date field,
rather than either numeric or text. You don't say what the field contains, but
you'll need some sort of expression to convert it to a date; if it contains
(say) 20080430 for today's date, use a calculated field

DateSerial([datefield] \ 10000, [datefield] MOD 10000 \ 100, [datefield] MOD
100)

and append it to a Date/Time field in your new table.

I don't have much coding experience, but know that numeric and text has to
have certain quotes around them. I tried figuting out myself with no luck.
The code below is what the debug brings me too. Can someone look at this and
let me know what need to be change for this numeric UPCCase field to work.

Number fields should not have any quotemarks around them. The field UPCCase in
your query doesn't HAVE any quotes, so it shouldn't be causing the problem!
How is the VBA variable strUPCs defined? What's the datatype of [Invoice
Week]? That does have quotes and probably shouldn't:

DoCmd.RunSQL "INSERT INTO [VOID - Concantenated Master List] ( CustNbr,
Store, City, State, UPCCase, Expr1, SVItemCd, SVDescription, [Beg Date], [End
Date] ) SELECT DISTINCT [Heinz Master].CustNbr, [All in one store
reference].Store, [Heinz Master].City, [Heinz Master].State, [Heinz
Master].UPCCase, [CustNbr] & [UPCCase] AS Expr1, [Heinz Master].SVItemCd,
[Heinz Master].SVDescription, [Week Numbers].[Beg Date], [Week Numbers].[End
Date] " & _
"FROM ([All in one store reference] LEFT JOIN [Heinz Master] ON [All
in one store reference].[Store Num] = [Heinz Master].CustNbr) LEFT JOIN [Week
Numbers] ON [Heinz Master].[Invoice Week] = [Week Numbers].[Wk#] WHERE
([Heinz Master].UPCCase IN (" & strUPCs & ")) AND ([Heinz Master].[Invoice
Week] >= " & begn & " And [Heinz Master].[Invoice Week] <= " & endn & ")
GROUP BY [Heinz Master].CustNbr, [All in one store reference].Store, [Heinz
Master].City, [Heinz Master].State, [Heinz Master].UPCCase, [CustNbr] &
[UPCCase], [Heinz Master].SVItemCd, [Heinz Master].SVDescription, [Week
Numbers].[Beg Date], [Week Numbers].[End Date];"

 

[John W. Vinson]

I tried to append to a new database, but get an Invalid Argument error. The
original table has 5,761,555 lines of data in it. Is the error caused by the
size of this table?

No, it's caused by an invalid argument, I presume. Is this table in a .mdb
file? If so how large is it? You're limited to 2 GByte in the database, total,
so a table of this size may be pushing it.

Perhaps you could post the SQL view of your query and an example of the data.
Remember - YOU can see your table; you know what it contains; I cannot and do
not.