Greek characters in query when pulling data from unbound form control

[Gina Maylone]

Has anyone ever had this happen? I get these "ᅀ䃥" when I am trying to get the data from an unbound date field on my form, it will append the date correctly, but it will not concatenate the fields properly at runtime.

Note1: [forms]![runassessments]![assessmenttype] & " - " & "Due " & DatePart("m",[Forms]![runassessments]![dateassessed]) & "1" & DatePart("yy",[Forms]![runassessments]![dateassessed])

This is happening in more than one database, in another I am getting these characters: "䙠䃣"

SQL the Append Query is based on:

INSERT INTO Accounts ( MemberID, Type, LotNumber, DateAssessed, EnteredBy, Details, DBAmount, [Note] )
SELECT AddAsmttoAccounts1.MemberID, AddAsmttoAccounts1.AssessmentType, AddAsmttoAccounts1.LotNumber, AddAsmttoAccounts1.DateAssessed, AddAsmttoAccounts1.User, AddAsmttoAccounts1.Details, AddAsmttoAccounts1.Amount, AddAsmttoAccounts1.Expr1
FROM AddAsmttoAccounts1;

Final SQL:

SELECT Members.MemberID, MemberAssessments.AssessmentType, MemberAssessments.Amount, MemberAssessments.LotNumber, [Forms]![RunAssessments]![AssessmentDate] AS DateAssessed, "Auto" AS [User], [Forms]![RunAssessments]![Details] AS Details, [Forms]![RunAssessments]![assessmenttype] & " " & [AD] & " " & [lotnumber] AS AsmtNote, Format([Forms]![RunAssessments]![AssessmentDate],"mmm yy") AS AD, MemberAssessments.LotNumber, [forms]![runassessments]![assessmenttype] & " - " & "Due " & DatePart("m",[Forms]![runassessments]![dateassessed]) & "1" & DatePart("yy",[Forms]![runassessments]![dateassessed]) AS Expr1
FROM Members INNER JOIN MemberAssessments ON Members.MemberID = MemberAssessments.MemberID
WHERE (((MemberAssessments.AssessmentType)=[Forms]![RunAssessments]![AssessmentType]) AND ((Members.NoAssessments)=False) AND ((Members.Inactive)=False));

Any information or assistance you can provide will be greatly appreciated!!! Happy Friday all!

[CJ_London]

this issue is usually associated with memo fields in group by queries or when used as criteria or in calculations (such as concatenate). See this link http://allenbrowne.com/bug-18.html

[NTC]

with your query in design view: have you set up the parameter?

right click in the upper portion of the query design view and select: parameter

then enter your full form control name i.e.

[forms]![runassessments].[assessmenttype] and define the field type it is.

[Gina Maylone]

Thanks Micron and NTC! No memo fields and no parameters. I got it to work by add "format" to the field.

format(
[Forms]![runassessments]![dateassessed]
,"short date")

Did the same for currency as well.

[Micron]

Cool! I get credit just for reading!