append-text,number, date

[mrrayj60]

I need help to append a record to a table from an append query based on the fields there not equal to Combined(ubl(text10),complaintid(numberlong int),finedate(shortdate)), I cant seem to get the logic right without getting a data type mismatch...access2003. Append this forms record to that table as long as they dont equal this forms data. If you need more info I can comply, Thanks. Ray

INSERT INTO vrcfines ( finedate, ubl, Name, [vrccase#], complaintid, complaint, fineamnt )
SELECT forms!vrcedit!txtfinedate AS Expr1, forms!vrcedit!txtubl AS Expr2, Forms!vrcedit!NAME AS Expr3, Forms!vrcedit!txtvrccase AS Expr4, Forms!vrcedit!txtcomplaintid AS Expr5, Forms!vrcedit!txtcomplaint AS Expr6, Forms!vrcedit!txtfine AS Expr7
FROM vrcfines
WHERE ((([vrcfines]![finedate])<>[Forms]![vrcedit]![txtfinedate]));

 

[HiTechCoach]

Try creating a query witt just the select part like this as a test:

SELECT forms!vrcedit!txtfinedate AS Expr1, forms!vrcedit!txtubl AS Expr2, Forms!vrcedit!NAME AS Expr3, Forms!vrcedit!txtvrccase AS Expr4, Forms!vrcedit!txtcomplaintid AS Expr5, Forms!vrcedit!txtcomplaint AS Expr6, Forms!vrcedit!txtfine AS Expr7
FROM vrcfines
WHERE ((([vrcfines]![finedate])<>[Forms]![vrcedit]![txtfinedate]));

This should return a list of duplicate records created from the form's data. There should be a one record for every record in the table vrcfines WHERE ((([vrcfines]![finedate])<>[Forms]![vrcedit]![txtfinedate]))

Is this really what you want?

If you are wanting to insert the data from a form, then try this sybtax:

INSERT INTO vrcfines ( finedate, ubl, Name, [vrccase#], complaintid, complaint, fineamnt )
Values( "#" & forms!vrcedit!txtfinedate & "#", forms!vrcedit!txtubl, Chr$(34) & Forms!vrcedit!NAME & Chr$(34), Chr$(34) & Forms!vrcedit!txtvrccase & Chr$(34) , Forms!vrcedit!txtcomplaintid, Chr$(34) & Forms!vrcedit!txtcomplaint & Chr$(34) , Forms!vrcedit!txtfine)

I am not sure of the data types so you may need to adjust the delimiters for dates (#) and strings (").

 

[mrrayj60]

I've tried to go the insert method but get a error expected end of statement. I'm just trying a few simply fields before I work on dates and cash. It highlights the word vrcfines

INSERT INTO vrcfines ([ubl][name])
VALUES (vrcedit!txtubl), (vrcedit!txtname)


*10/10/09 problem resolved, I though we were talking about using it as code in a command button, you meant as a append query and once I relaized that, it worked.

 

[HiTechCoach]

Glad to hear you got it working!