Access Error message when Appending data

[TTF]

Hi! Can you please try to help me with this huge headache?

I am running code in Access2002

Private Sub cmd_Search_Click()

Dim mSQL As String, ListSQL As String, StrRS As String, txtStr As String

Set dbR = CurrentDb()
DoCmd.SetWarnings False

dbR.Execute "Delete from Search_Result"

mSQL = "INSERT INTO Search_Result" _
& "([Pay date],[EC Member], [Supervisor], [Last Name],[First Name], [Business Unit]," _
& "[Cost Center], [Amount Charged], [Manual Check Date])" _
& "SELECT [master-data-charges].[Pay date],[master-data-charges].[EC Member]," _
& "[master-data-charges].[Supervisor], [master-data-charges].[Last Name]," _
& "[master-data-charges].[First Name], [master-data-charges].[Business Unit]," _
& "[master-data-charges].[Cost Center], [master-data-charges].[Amount Charged]," _
& "[master-data-charges].[Manual Check Date]" _
& "FROM [master-data-charges] WHERE" _
& "[master-data-charges].[Pay date]=([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[EC Member]=([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[Supervisor]=([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[Last Name] = ([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[First Name]=([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[Business Unit]=([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[Cost Center]=([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[Amount Charged]=([Forms]![frm_Find_User]![txt_Search])" _
& "or [master-data-charges].[Manual Check Date] = ([Forms]![frm_Find_User]![txt_Search]);"
DoCmd.RunSQL mSQL

End Sub

When I run this code my table gets populated like this

Pay date EC Member Supervisor Last Name First Name
ED HUES Mel,Guimo Serno Lu
Bunit Cost Center Amount Charged Manual Check Date
DLS TE01 0 0

When I run the same query right from Access my table gets

Pay date EC Member Supervisor Last Name First Name Bunit
12/2/2004 ED HUES Mel,Guimo Serno Lu DLS

Cost Center Amount Charged Manual Check Date
TE01 300 3798

So Pay date, Amount charged and Manual Check Date fields aren't getting populated from code.

Search_Result table has the same datatypes as master-data-charges table.
So problem fields are Pay date, Amount charged and Manual Check Date fields.
Do you see any problem why wouldn't it get populated?
Thanks for helping

P.S. The error I am getting at line DoCmd.RunSQL mSQL is "Datatype mismatch in criteria expression"

I appreciate your time and THANKS

 

[PHV]

The txt_Search control can't be used at the same time for date, numeric and text comparisons.
You may try this (typed, untested):
mSQL = "INSERT INTO Search_Result " _
& "([Pay date],[EC Member],[Supervisor],[Last Name],[First Name]," _
& "[Business Unit],[Cost Center],[Amount Charged],[Manual Check Date])" _
& " SELECT [Pay date],[EC Member],[Supervisor],[Last Name],[First Name]," _
& "[Business Unit],[Cost Center],[Amount Charged],[Manual Check Date]" _
& " FROM [master-data-charges] WHERE [Forms]![frm_Find_User]![txt_Search]" _
& " In ([Pay date],[EC Member],[Supervisor],[Last Name],[First Name]," _
& "[Business Unit],[Cost Center],[Amount Charged],[Manual Check Date])"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244