Unknown Jet Error

  • Thread starter Joker via AccessMonster.com
  • Start date
J

Joker via AccessMonster.com

Hello,

I have a make table query I have set up. It ran perfectly yesterday but upon
coming in today it was giving me a Unknown Jet Error. When I clicked help it
brought up "There was a type mismatch when creating a table validation rule
or a CHECK constraint on a column. (Error 3072)."

That error has me really confused. I don't think there is anything in the
query that would result in a type mismatch. I have played with my references
some to no benifit.

The SQL of my query is below.

SELECT DISTINCT fncDueDate(tbl_RemitDays!Frequency,tbl_RemitDays!Days,
tbl_MAIN!Proc_Date) AS [TruDueDate(R)], tbl_RemitDays.Days, tbl_RemitDays.
Frequency, tbl_MAIN.DateImported, tbl_RemitDays.[Inv #], fncDueDate
(tbl_RemitDays!PayoffFreq,tbl_RemitDays!PayoffDays,tbl_MAIN!Proc_Date) AS
[TruDueDate(P)], tbl_MAIN.Payoffs, tbl_MAIN.ScheduledRemit_Day, IIf
(tbl_TrackRegIntRemits!IntRemits Is Not Null,tbl_TrackRegIntRemits!IntRemits,
IIf([Payoffs] Is Null,[TruDueDate(R)],[TruDueDate(P)])) AS TruDD, tbl_MAIN.
PI_Acct_Num, tbl_MAIN.Reporter, tbl_MAIN.Type, tbl_MAIN.[Remit Type],
DateDiff("d",[DateImported],[TruDD]) AS DateDif, tbl_MAIN.Proc_Date INTO
tbl_END_RESULT
FROM tbl_TrackRegIntRemits RIGHT JOIN (tbl_MAIN INNER JOIN tbl_RemitDays ON
tbl_MAIN.[inv#] = tbl_RemitDays.[Inv #]) ON tbl_TrackRegIntRemits.[inv#] =
tbl_RemitDays.[Inv #]
ORDER BY tbl_RemitDays.[Inv #];

Any ideas or thoughts would be GREATLY appreciated. Thanks.

-Joker
 
A

Allen Browne

The type mismatch indicates Access is struggling to match one data type with
another.

Examples:
- The fields where you joined the tables may not match.
- The fields you are comparing in the IIf() expressions may not match.
- The data returned from the function may not be consistent.

One you identify the cause, the solution will be to typecast to the data
type you want. Details and examples in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Joker via AccessMonster.com said:
Hello,

I have a make table query I have set up. It ran perfectly yesterday but
upon
coming in today it was giving me a Unknown Jet Error. When I clicked help
it
brought up "There was a type mismatch when creating a table validation
rule
or a CHECK constraint on a column. (Error 3072)."

That error has me really confused. I don't think there is anything in the
query that would result in a type mismatch. I have played with my
references
some to no benifit.

The SQL of my query is below.
Click to expand...
SELECT DISTINCT fncDueDate(tbl_RemitDays!Frequency,
tbl_RemitDays!Days,
tbl_MAIN!Proc_Date) AS [TruDueDate(R)],
tbl_RemitDays.Days, tbl_RemitDays.
Frequency, tbl_MAIN.DateImported,
tbl_RemitDays.[Inv #], fncDueDate
(tbl_RemitDays!PayoffFreq,
tbl_RemitDays!PayoffDays,tbl_MAIN!Proc_Date) AS [TruDueDate(P)],
tbl_MAIN.Payoffs, tbl_MAIN.ScheduledRemit_Day,
IIf (tbl_TrackRegIntRemits!IntRemits Is Not Null,
tbl_TrackRegIntRemits!IntRemits,
IIf([Payoffs] Is Null,
[TruDueDate(R)],
[TruDueDate(P)])) AS TruDD,
tbl_MAIN.PI_Acct_Num,
tbl_MAIN.Reporter,
tbl_MAIN.Type,
tbl_MAIN.[Remit Type],
DateDiff("d",[DateImported],[TruDD]) AS DateDif,
tbl_MAIN.Proc_Date
INTO tbl_END_RESULT
FROM tbl_TrackRegIntRemits
RIGHT JOIN (tbl_MAIN
INNER JOIN tbl_RemitDays
ON tbl_MAIN.[inv#] = tbl_RemitDays.[Inv #])
ON tbl_TrackRegIntRemits.[inv#] = tbl_RemitDays.[Inv #]
ORDER BY tbl_RemitDays.[Inv #];
 
J

Jamie Collins

The type mismatch indicates Access is struggling to match one data type with
another.
Click to expand...

....But the full error description goes on to say "when creating a
table validation rule or a CHECK constraint on a column." With a 'make-
table query', the engine is responsible for creating the target table
and, while I've long suspected the engine uses 'internal' CHECK
constraints for its own purposes, I can't spot anything in the OP's
SQL to suggest this would be happening, though we've no way of knowing
what 'fncDueDate' may be doing.

It could be the case the error description is bogus and has nothing to
do with the real problem, which IIRC I've seen with other error
descriptions from Jet.

Jamie.

--
 
J

Joker via AccessMonster.com

THANK YOU VERY MUCH! You were a great help. It seems that I had set one of
the parts of my user defined function as a variant and it was returning text
when I needed a date. I really appreciate the assistance.

-Joker
 
Top