Syntax error VB6 & Jet4 Database

[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


This has been resolved.
Many thanks to ae_jester

I formatted the text to numeric with no commas and attached the ado to a variable then re formatted for currency.
Works great.
Many thankful regards,
Dwight

[RobDog888]

Welcome to the forums.

Make sure Investor_Number is a numeric field type and that you
format the Value textbox to not contain the $ symbol and the
comma. Access thinks the comma is separating a field in the
where clause and that field has no name.

Ex. "...AND Value = $29, AND ??? = 697.00".

HTH

[DK47]

RobDog'
Thanks very much.
DK

[RobDog888]

You can keep the formatting in the textbox, but when you get
ready to submit your sql, format the value so it doesn't have it.

Glad to help.