Data type mismatch

[SBBmaster09]

Hi,

Ive been encountering this error, "Data Type Mismatch", can anyone help why i am getting the error?

    If IsNull(DLookup("[SubCount]", "qry_SubCount2", "[MTransID] = '" & Me.txtTxnID2.Value & "'")) Then
        Me.txtSubCount.Value = 0
        SubCountID = CLng(Nz(Me.txtSubCount, 0)) + 1
    Else
        Me.txtSubCount = DLookup("[SubCount]", "qry_SubCount2", "[MTransID] = '" & Me.txtTxnID2.Value & "'")
        SubCountID = CLng(Nz(Me.txtSubCount, 0)) + 1
    End If

:banghead:

 

[pr2-eugin]

I believe that the MTransID is Number type as opposed to Text.

 

[SBBmaster09]

Its an expression from a query,

MTransID: (Left([TicketID],InStr(1,[TicketID],"-")-1))

How would I know if it is numeric?

 

[jdraw]

What is the data type of TicketID in the table involved? If it has an embedded "-", I don't see how it could be numeric.
Have you tried step debugging? see this for info

 

[SBBmaster09]

I am not familiar on how would I use the link you gave me but anyway thanks for that info,

The
TicketID data type is "Text"

 

[JHB]

Show the SQL-String for the query "qry_SubCount2"

 

[SBBmaster09]

qry_SubCount2:

SELECT qry_SubCount.MTransID, Count(*) AS SubCount
FROM qry_SubCount
GROUP BY qry_SubCount.MTransID;

qry_SubCount:

SELECT MainTable_Broadband.Activity, MainTable_Broadband.TicketID, (Left([TicketID],InStr(1,[TicketID],"-")-1)) AS MTransID
FROM MainTable_Broadband
GROUP BY MainTable_Broadband.Activity, MainTable_Broadband.TicketID
HAVING (((MainTable_Broadband.Activity) Like "Processing Order" Or (MainTable_Broadband.Activity) Like "Processing Pending Order"));

 

[SBBmaster09]

Hi can anyone help me with this? thanks.

 

[Galaxiom]

Access can change a datatype in a query. It may be casting the text to a number because it sees the results of the expression is a number. Force the type to what you want.

BTW There are a number of issues with your design.

The need to parse the field suggests you should consider storing the information as separate fields and concatenating with the dash for display.

Like is pointless if the comparison doesn't include a wildcard. Use equals.

However it would be better to store the Activity as a numeric representation with a lookup table for the text they represent. This is far more efficient to store and query.

Use a sequence of numbers if the steps are sequential so you can test like:
WHERE Activity BETWEEN 4 AND 5

Your sequence of queries is unnecessary. It seems to me that your HAVING could be WHERE and the Count included in the first query.

Indeed you probably could use a DCount without requiring the queries at all.