Can I use WHERE SOMETHING>0 on an SQL currency field (usi

A

alexwren

New Member
Joined
Jan 3, 2007
Messages
16
Hello,

Having problems with two things.

I have a nice little bit of Excel VBA that queries a table within an Access db. Problem being is that the client has formatted the field as currency and the following code does not work.

Code: 
sSQL = sSQL & " WHERE DNname.[LAST YEAR]>0 AND DNname.[THIS YEAR]=>0 AND ...


Also does anyone know if you can use less than on a text field?

i.e.

Code: 
AND DNname.[CATEGORY]<'40' AND...


Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
for question 1, if you use >= instead of => does it work?

for question 2, you can totally do that as long as you understand that the number will be evaluated as a string so '100' is less than '40'. Access does provide string conversion functions if this doesn't work for you (cint, cdbl, clng,cCur, can't remember if there are others... check out the help)

hth,
Giacomo
 
Upvote 0
Yes, you can use less than on a text field, but usually you are not going to get what you want. "6" is less than "40" so you need to be VERY careful when dealing with less than or greater than with a text field.

If this text field will ALWAYS have a number in it, you can compare the numbers as number by first using the Val() function like this:
If Val(TextFld1) < Val(TextFld2) Then ...

HTH,
 
Upvote 0
And she (Val) is cute too! This is scary that we both choose '40' as part of our example. (y)
 
Upvote 0
Thanks guys,

So I have now got

Code: 
WHERE Cint(DBName.[LAST YEAR])>0 AND Cint(DBName.[THIS YEAR])>0 AND Cint(DBName.[CATEGORY])<40 AND ...

Unfortunately this still gives me an VBA error.

Have I got the formating wrong?
 
Upvote 0
could we see the whole SQL statement? Actually, all the VBA code that is being used here? So much easier when we can see what is happening rather than a small piece of it. Oh yes, what is the error?
Thanks,
 
Upvote 0
Hi VicRauch,

Thanks for your prompt reply.

Sorry about the lack of info, I should know better. If anyone has any suggestions on how I can lay out each of the WHERE statements on a new line that would be very helpful.

Code: 
'Set source

MyConn = "C:\Documents and Settings\UserName\My Documents\DBName.mdb"
    
'Create querys

SQL = "SELECT DBName.[COMPANYNAME], DBName.[ADD1], DBName.[ADD2], DBName.[ADD3], DBName.[TOWN], DBName.[COUNTY], DBName.[POSTCODE], DBName.[COUNTRY], DBName.[PHONE], DBName.[Web Site], DBName.[WEB AREA]"

sSQL = sSQL & " FROM DBName"

sSQL = sSQL & " WHERE Cint(DBName.[LAST YEAR])>0 AND Cint(DBName.[THIS YEAR])>0 AND Cint(DBName.[CATEGORY])<40 AND DBName.[COMPANYNAME]IS NOT NULL AND DBName.[COUNTY]IS NOT NULL AND DBName.[TOWN]IS NOT NULL AND DBName.[PHONE]IS NOT NULL AND DBName.[WEB AREA]=" & w & ""

sSQL = sSQL & " ORDER BY DBName.[COUNTY], Brookfox1.[TOWN], DBName.[COMPANYNAME] ;

'Create RecordSet

    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
        Set Rs = .Execute(sSQL)
    End With"

Error is

"Invalid use of Null"
 
Upvote 0
Whoops just edited the post to add the missing

Code: 
Cint(DBName.[LAST YEAR])>0 AND Cint(DBName.[THIS YEAR])>0 AND

to the above
 
Upvote 0
That means that the value of one of the fields that you are using Cint with is Null. You need to find out why it is null, or put Nz() around the variable so it will be turned into an empty string, or a zero if it should be a number.
HTH,
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question
How to structure a variable product rating database
Replies
5
Views
536
mdmilner
M
M
  • Solved
Using VBA to change a single parameter in a long SQL statement
Replies
7
Views
923
Micron
M
W
  • Solved
Access Query - is it possible?
Replies
5
Views
2K
xenou
xenou
J
  • Question
ODBC--call failed #241
2
Replies
14
Views
4K
Micron
M
C
  • Question
What libraries can be used to access SQL Server databases using Excel/VBA on a Mac?
Replies
0
Views
280
chuckbo
C

Forum statistics

Threads
1,217,382
Messages
6,136,237
Members
450,000
Latest member
jgp19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top