Syntax for SQL CAST fn in VBA

[Jeff31592]

Need to use CAST to return integer value of string (digits as data type string).

Where clause looks like this:

... Where Cast([Price File] as int) > 0

works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the debug.print to verify that SQL has no goofs ... looks good but not to Access. What is proper syntax?

 

[pbaldy]

No CAST in Access. Try

CInt([Price File])

Or CLng(), depending on your requirements.

 

[Jeff31592]

will try this.

 

[MSAccessRookie]

Also, considering that the name of the String [Price File] implies a decimal, you might also want to try CDbl().

-- Rookie

 

[pbaldy]

The int type in SQL Server doesn't include a decimal.

 

[Jeff31592]

CInt([Price File]) > 0 throws an error. I think there must be some sort of Access syntax I am missing

" Invalid SQL statement; expected 'Delete', 'Insert', 'Procedure' ....

Actual debug.print:

Select [PIR Vendor], Vendor_Name, Material, Description, MIC, [Base UOM], [Net Price] FROM Cost_File Where [PIR Vendor] = '11084' AND CInt([Price File]l) > 99999

 

[Jeff31592]

not sure what that little mark is at the end of my SQLString but it is not in the Access code line

 

[pbaldy]

Given the size of the value you'd want Clng(). You also have an extra character after the brackets.

 

[Jeff31592]

all values for [Price File] are 4, 5, or 6 characters (digits). The mark in the brackets is the letter 'L' and is not in the SQLString (proprietary data that I modify so I can show in Forum - forgot to erase the L from my original when I cut and pasted to my post).

That said, the query throws an error when using CInt([Price File]) in the Where clause. Is there some parens or other syntax that needs to be used to enclose this fn?

 

[pr2-eugin]

It throws an Error makes no sense.. what error does it throw? Error Number and Description..

Also is there a possibility that the [Price File] value could be Null?

 

[pbaldy]

Also, change to CLng() as I mentioned before. CInt() can only handle numbers to 32k.

 

[Jeff31592]

yes CLng fn works. Thanks.

 

[pbaldy]

No problem.