From Microsoft Access 2003 we runn a query against a Prevasive SQL 8
data base.  When the between statement is pre populated it works fine.
 When the between statement is populate via user input it fails.  Here
are the two statements.  Can anyone give me a solution.


This one works.

SELECT JC_JOB_MASTER.Job_Number, JC_JOB_MASTER.Division,
JC_JOB_MASTER.Job_Description, JC_TRANSACTION_HISTORY.Phase_Code,
JC_TRANSACTION_HISTORY.Cost_Type,
JC_TRANSACTION_HISTORY.Tran_Type_Code,
JC_TRANSACTION_HISTORY.Tran_Date_Text,
JC_TRANSACTION_HISTORY.Employee_Code,
JC_TRANSACTION_HISTORY.Vendor_Code,
JC_TRANSACTION_HISTORY.Total_Hours, JC_JOB_MASTER.Superintendent,
JC_JOB_MASTER.Estimator, JC_JOB_MASTER.Job_Type,
JC_JOB_MASTER.Project_Manager, Mid([Tran_Date_Text],5,2) AS [Month]
FROM JC_JOB_MASTER INNER JOIN JC_TRANSACTION_HISTORY ON
JC_JOB_MASTER.Job_Number = JC_TRANSACTION_HISTORY.Job_Number
WHERE (((JC_JOB_MASTER.Division)="01") AND
((JC_TRANSACTION_HISTORY.Tran_Date_Text) Between "20050501" And
"20050530") AND ((JC_JOB_MASTER.Job_Type)="AMBE" Or
(JC_JOB_MASTER.Job_Type)="APAR" Or (JC_JOB_MASTER.Job_Type)="CELL" Or
(JC_JOB_MASTER.Job_Type)="COMM" Or (JC_JOB_MASTER.Job_Type)="DOWN" Or
(JC_JOB_MASTER.Job_Type)="HOTE" Or (JC_JOB_MASTER.Job_Type)="LIGH" Or
(JC_JOB_MASTER.Job_Type)="RESI" Or (JC_JOB_MASTER.Job_Type)="RICH" Or
(JC_JOB_MASTER.Job_Type)="VANC" Or (JC_JOB_MASTER.Job_Type)="1AMBE" Or
(JC_JOB_MASTER.Job_Type)="1APAR" Or (JC_JOB_MASTER.Job_Type)="1COMM"
Or (JC_JOB_MASTER.Job_Type)="1CELL" Or (JC_JOB_MASTER.Job_Type)="1
DOWN" Or (JC_JOB_MASTER.Job_Type)="1HOTE" Or
(JC_JOB_MASTER.Job_Type)="1LIGH" Or (JC_JOB_MASTER.Job_Type)="1RESI"
Or (JC_JOB_MASTER.Job_Type)="1RICH" Or
(JC_JOB_MASTER.Job_Type)="1VANC"));










This one does not-

SELECT JC_JOB_MASTER.Job_Number, JC_JOB_MASTER.Division,
JC_JOB_MASTER.Job_Description, JC_TRANSACTION_HISTORY.Phase_Code,
JC_TRANSACTION_HISTORY.Cost_Type,
JC_TRANSACTION_HISTORY.Tran_Type_Code,
JC_TRANSACTION_HISTORY.Tran_Date_Text,
JC_TRANSACTION_HISTORY.Employee_Code,
JC_TRANSACTION_HISTORY.Vendor_Code,
JC_TRANSACTION_HISTORY.Total_Hours, JC_JOB_MASTER.Superintendent,
JC_JOB_MASTER.Estimator, JC_JOB_MASTER.Job_Type,
JC_JOB_MASTER.Project_Manager, Mid([Tran_Date_Text],5,2) AS [Month]
FROM JC_JOB_MASTER INNER JOIN JC_TRANSACTION_HISTORY ON
JC_JOB_MASTER.Job_Number=JC_TRANSACTION_HISTORY.Job_Number
WHERE (((JC_JOB_MASTER.Division)="01") AND
((JC_TRANSACTION_HISTORY.Tran_Date_Text) Between [Type the beginning
date:] And [Type the ending date:]) AND
((JC_JOB_MASTER.Job_Type)="AMBE" Or (JC_JOB_MASTER.Job_Type)="APAR" Or
(JC_JOB_MASTER.Job_Type)="CELL" Or (JC_JOB_MASTER.Job_Type)="COMM" Or
(JC_JOB_MASTER.Job_Type)="DOWN" Or (JC_JOB_MASTER.Job_Type)="HOTE" Or
(JC_JOB_MASTER.Job_Type)="LIGH" Or (JC_JOB_MASTER.Job_Type)="RESI" Or
(JC_JOB_MASTER.Job_Type)="RICH" Or (JC_JOB_MASTER.Job_Type)="VANC" Or
(JC_JOB_MASTER.Job_Type)="1AMBE" Or (JC_JOB_MASTER.Job_Type)="1APAR"
Or (JC_JOB_MASTER.Job_Type)="1COMM" Or
(JC_JOB_MASTER.Job_Type)="1CELL" Or (JC_JOB_MASTER.Job_Type)="1 DOWN"
Or (JC_JOB_MASTER.Job_Type)="1HOTE" Or
(JC_JOB_MASTER.Job_Type)="1LIGH" Or (JC_JOB_MASTER.Job_Type)="1RESI"
Or (JC_JOB_MASTER.Job_Type)="1RICH" Or
(JC_JOB_MASTER.Job_Type)="1VANC"));