Make table Query - saving field as binary(510)

  • Thread starter Thread starter peg
  • Start date Start date
P

peg

I'm going crazy trying to figure this one out. I have an unbound textbox on
a form. When the user click on print report it merges 2 other textboxes
into this on unbound one, then it calls an make table query which is pulling
the value from the unbound text box.

My problem is that when it creates the table it puts that field as a
binary(510) which allows my about 127 characters.

the "[Forms]![PopUpAddNotes]![Notes3].[value] AS AdditionalNotes" is where
this is happening.

SELECT TempBenefits.PlanName, TempBenefits.Carriername,
TempBenefits.[AnnualDeductible-individual],
TempBenefits.[AnnualDeductible-family],
TempBenefits.[AnnualOOPMax-individual], TempBenefits.[AnnualOOPMax-family],
TempBenefits.DrVisit, TempBenefits.UrgentCarefacility,
TempBenefits.EmergencyRoomvisit,
TempBenefits.[AnnualDeductible-individual-non],
TempBenefits.[AnnualDeductible-family-non],
TempBenefits.[AnnualOOPMax-individual-non],
TempBenefits.[AnnualOOPMax-family-non], TempBenefits.[DrVisit-non],
TempBenefits.NameofNetwork, TempRxBenefits.RxPlanname,
TempRxBenefits.tierone, TempRxBenefits.tiertwo, TempRxBenefits.tierthree,
TempRxBenefits.mailordertierone, TempRxBenefits.mailordertiertwo,
TempRxBenefits.mailordertierthree, "$ " &
[forms]![frmcreatequote]![txtmonthly3] AS Expr1,
TempBenefits.PreventiveCare, TempBenefits.PlanMaximum,

[Forms]![PopUpAddNotes]![Notes3].[value] AS AdditionalNotes,

TempRxBenefits.AdditionalInfo1, TempRxBenefits.AdditionalInfo2, "$ " &
[Forms]![PopUpYesCR]![txtEmp3] AS EmpOnly, "$ " &
[Forms]![PopUpYesCR]![txtEmpF3] AS EmpF, "$ " &
[Forms]![PopUpYesCR]![txtEmpS3] AS EmpS, "$ " &
[Forms]![PopUpYesCR]![txtEmpC3] AS EmpC, "$ " &
[Forms]![frmCreateQuote]![txtTotal3] AS Total, "$ " &
[Forms]![PopUpYesCR]![txtEmpCn3] AS EmpCN, "$ " &
[Forms]![PopUpYesCR]![txtEmpO3] AS EmpCO, "$ " &
[forms]![frmcreatequote]![txtOtherFee3] AS OtherFees,
TempBenefits.[UrgentCarefacility-non], TempBenefits.CoInsurance,
TempBenefits.[CoInsurance-non] INTO [Report Source3]
FROM TempBenefits LEFT JOIN TempRxBenefits ON TempBenefits.Carriername =
TempRxBenefits.Carriername
WHERE (((TempBenefits.PlanName)=[Forms]![frmCreateQuote]![cboPlan3]) AND
((TempBenefits.Carriername)=[Forms]![frmCreateQuote]![cboCarrier3]) AND
((TempRxBenefits.RxPlanname)=[Forms]![frmCreateQuote]![cboRxProg3]));

Never ran into this before - so any help would be GREATLY appreciated!
 
Hi Peg,

Generally if I am concerned about field data types, I use
delete and append queries to add records to a predefined
table structure. But, with that said, generally you will
get pretty good results using the numerical conversion
functions to get the data type that you want. In your
case, you may want to try putting your field value inside
the Cbool() function to see if it will correct the
problem.

HTH, Ted Allen
-----Original Message-----
I'm going crazy trying to figure this one out. I have an unbound textbox on
a form. When the user click on print report it merges 2 other textboxes
into this on unbound one, then it calls an make table query which is pulling
the value from the unbound text box.

My problem is that when it creates the table it puts that field as a
binary(510) which allows my about 127 characters.

the "[Forms]![PopUpAddNotes]![Notes3].[value] AS AdditionalNotes" is where
this is happening.

SELECT TempBenefits.PlanName, TempBenefits.Carriername,
TempBenefits.[AnnualDeductible-individual],
TempBenefits.[AnnualDeductible-family],
TempBenefits.[AnnualOOPMax-individual], TempBenefits. [AnnualOOPMax-family],
TempBenefits.DrVisit, TempBenefits.UrgentCarefacility,
TempBenefits.EmergencyRoomvisit,
TempBenefits.[AnnualDeductible-individual-non],
TempBenefits.[AnnualDeductible-family-non],
TempBenefits.[AnnualOOPMax-individual-non],
TempBenefits.[AnnualOOPMax-family-non], TempBenefits. [DrVisit-non],
TempBenefits.NameofNetwork, TempRxBenefits.RxPlanname,
TempRxBenefits.tierone, TempRxBenefits.tiertwo, TempRxBenefits.tierthree,
TempRxBenefits.mailordertierone,
Click to expand...
TempRxBenefits.mailordertiertwo,
TempRxBenefits.mailordertierthree, "$ " &
[forms]![frmcreatequote]![txtmonthly3] AS Expr1,
TempBenefits.PreventiveCare, TempBenefits.PlanMaximum,

[Forms]![PopUpAddNotes]![Notes3].[value] AS AdditionalNotes,

TempRxBenefits.AdditionalInfo1,
Click to expand...
TempRxBenefits.AdditionalInfo2, "$ " &
[Forms]![PopUpYesCR]![txtEmp3] AS EmpOnly, "$ " &
[Forms]![PopUpYesCR]![txtEmpF3] AS EmpF, "$ " &
[Forms]![PopUpYesCR]![txtEmpS3] AS EmpS, "$ " &
[Forms]![PopUpYesCR]![txtEmpC3] AS EmpC, "$ " &
[Forms]![frmCreateQuote]![txtTotal3] AS Total, "$ " &
[Forms]![PopUpYesCR]![txtEmpCn3] AS EmpCN, "$ " &
[Forms]![PopUpYesCR]![txtEmpO3] AS EmpCO, "$ " &
[forms]![frmcreatequote]![txtOtherFee3] AS OtherFees,
TempBenefits.[UrgentCarefacility-non], TempBenefits.CoInsurance,
TempBenefits.[CoInsurance-non] INTO [Report Source3]
FROM TempBenefits LEFT JOIN TempRxBenefits ON TempBenefits.Carriername =
TempRxBenefits.Carriername
WHERE (((TempBenefits.PlanName)=[Forms]![frmCreateQuote]! [cboPlan3]) AND
((TempBenefits.Carriername)=[Forms]![frmCreateQuote]! [cboCarrier3]) AND
((TempRxBenefits.RxPlanname)=[Forms]![frmCreateQuote]! [cboRxProg3]));

Never ran into this before - so any help would be GREATLY appreciated!


.
Click to expand...
 
Back
Top