help with syntax

[DK47]

Hi,
This is my first time at VB Forums.
I think this is the correct area for posting coding questions.
If it is not, please let me know.

Question:
I have been building a VB6 application with Jet 4 Microsoft Data Base for tracking investments.
Since I am relatively new to VB I continue to have difficulty with the syntax.

The following code generates a syntax error when I run the application.

adoTrackInvest.RecordSource = "SELECT Invested FROM History WHERE Date >= # " & txtTrackingDate & " # AND Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Value = " & txtHiVal & "'"
adoTrackInvest.Refresh

What is puzzeling to me is that the Error Message Box that warns of the syntax error seems to be able to draw the required info from the text boxes.

The message I get is:
Syntax error in query expression 'Date>= #4/3/2004# AND Investor_Number = 5 AND Value = $29,697.00".

All these values are correct.

Any help on this will be greatly appreciated.
Thanks,
Dwight

[ae_jester]

My first guess would be this.

You have spaces before and after the date (after the first # and before the 2nd #). I'm not sure if that will cause a syntax error in SQL or not, but it might, so change it to this

"WHERE Date >= #" & txtTrackingDate & "# AND" 'etc

Second, make sure both Investor_Number and Value are numeric fields in the database, if not you will have to include a ' before and after like this...

Investor_Number = '" & frmStartPage.txtInvestorNumber & "' AND " 'etc

[ae_jester]

Actually, now that I look at it, i think the problem is with your field named Date.

Date is a reserved keyword in SQL (i think), so you might want to consider naming that field something else.

[szlamany]

If it's MS SQL on the other end of the, then you have several problems in your query itself.

First of all, delimit dates with single quotes - not pound signs.

Second, that dollar value is truly ugly with the $ and the , in it.

SQL doesn't want "display" characters in numeric values.

If in doubt, use QUERY ANALYZER to execute the same query and see what errors you are getting. If you do not have QUERY ANALYZER, EXCEL should be able to open a data source to the database and you can test your queries with that.

[DK47]

Thanks to all of you for helping.
The info on how ugly the ($) and (,) were, got me thinking in the correct direction.

I isolated the query by breaking it down to each filtered portion and found that the value portion caused my error.

Also I defined the the Date field using (Date) to avoid using an SQL keyword. Thanks for the heads up on that.

Here is how I finally got the query to return without an error.

I loaded the txtBox info into a masked edit box and formatted it for numbers like this (#######.00)

Bingo! Worked like a dream.

adoTrackInvest.RecordSource = "SELECT (Invested) FROM History WHERE ( Date) >= # " & txtTrackingDate & " # AND Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Value = " & meHiVal
adoTrackInvest.Refresh


Your help has been wonderful.
Regards,
Dwight

Ya know,
The more ya learn,
The more ya know, ya don't know.

[Shaggy Hiker]

I'm a little surprised that it is working. Some of those query arguments look like they are strings (but only because they come from what appears to be text boxes). Strings would have to be enclosed in quotes, or converted to numbers using Val. Must be something I'm missing.

[DK47]

Shaggy,
Here's the scoop on the entire query.

adoTrackInvest.RecordSource = "SELECT (Invested) FROM History WHERE ( Date) >= # " & txtTrackingDate & " # AND Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Value = " & meHiVal
adoTrackInvest.Refresh

Invested is a field set for Currency.

Date is a field set for Date.

txtTrackingDate returns a date and is bound to another ado.

Investor_Number is a field set for Integer.

frmStartPage.txtInvestorNumber is bound to another ado and returns the investor number relating to a user input password.

Value is a field set for currency.

meHiVal is a masked edit box I used to take the returned currency value from txtHiVal which is bound to another ado.

When I connected the above query to txtHiVal directly, with the syntax exactly as shown, I got syntax errors.

After formatting the masked edit box to just numbers(#####.00) and loading it from txtHiVal, the query returned all the data as required.

I'm not kidding.
It works just fine.


This is part of an investment tracking program I am designing. The code above is part of a procedure which allows the investor to compare gains or losses from a specified date retrieved from the History table in relation to the the date the update is performed.
That way the investor can check the staus of his investments from the inseption of the program or any update in the history table.





Regards,
Dwight