Read a file and import its data into a table (1 Viewer)

[anishkgt]

Here MIRSeq is a long number, MIRType is an Alphabet, Tkt a string. I 've just copied them from unerlying elseIf Query. can't figure out the commas.

 

[anishkgt]

or is it because i've added one more and statement to cut out on the second else if statement

 

[namliam]

Quotes are a little easier to figure out if you keep things readable.

your current re-spaced a little:

If DCount("FileID", "qryMIR", "[MIRSeq]      =  " & MIRSeq & _ 
                         " and [MIRType]     =""" & MIRType & """" & _ 
                         " and [TicketNumber]=""" & tkt & """")

Which assumes
MIRSeq is number
MIRType and TicketNumber are text

Only other problem may be if there is some data conversion being done in the query which may cause issues if that error's out and you are trying to do this DCount on it.

 

[anishkgt]

ok seems like that all the MIRType used if validating is omits some useful data too. How do i set the mirtype part to a predefined alphabet like 'A' something like

DCount("FileID", "qryVLD", "[MIRType]="A"" and [TicketNumber]=""" & tkt & """")

 

[namliam]

Either
DCount("FileID", "qryVLD", "[MIRType]=""A"" and [TicketNumber]=""" & tkt & """")
Notice the red quotes are doubles, they "escape" eachother becomming a single " in the actual string

or
DCount("FileID", "qryVLD", "[MIRType]='A' and [TicketNumber]=""" & tkt & """")
requirement here is that MIRType now cannot contain a ' in the value otherwize the statement will fail. Which is why we mostly prefer to use " as a text definition, because " is much more rarely used in text fields.

 

[anishkgt]

Tried the below code but it did not omit the duplicates.

DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [MIRType]=""A"" and [TicketNumber]=""" & tkt & """")

but it worked when using it with ElseIf

 

[anishkgt]

oh and the tutorial on the comas was helpful, Thanks

 

[namliam]

With the tripple check it requires all 3 to be there as "one" record

Your else if checks for 2 to exist, then 2 others to exist....
They are completely different "questions" which one is right depends on your logic....

 

[anishkgt]

ah i then i guess thats the use of the 'and' part in it. So i guess best is to stick with the ElseIf, i guess. Now time to check on the A04 thing. will check and update , i guess the CounterA04 should be good work for me.

 

[namliam]

Well logic is logic.... Much like the SQL you can "OR" the dcount.

Problem then is how to distinquish the two different problems
Something like:

DCount("FileID", "qryMIR", "      [TicketNumber]=""" & tkt & """" & _
                      " and (     [MIRSeq]=" & MIRSeq & " " & _
                           " or   [MIRType]=""A"" ) ")

 

[anishkgt]

DCount("FileID", "qryVLD", "([MIRType]=""A Or H"") and ([TicketNumber]=""" & tkt & """)")

trying to do a dup check where A and H are predefined and if other characters then it should omit this check, just wondering if the code is correct. I tried it out but once the data is already there with the above conditions this line is omited, where it actually should not.

 

[anishkgt]

I guess it was
DCount("FileID", "qryVLD", "([MIRType]=""A"" Or ""H"") and ([TicketNumber]=""" & tkt & """)")

 

[anishkgt]

ok ! thought i nailed it but when the MIRType is R it still say duplicate but it should not

 

[namliam]

Much like the query designer you are trying to do it, while you need to think SQL...
DCount("FileID", "qryVLD", "([MIRType]=""A"" OR [MIRType] = ""H"") and ([TicketNumber]=""" & tkt & """)")

 

[anishkgt]

Tried that but result is the same MIRType with R is also omited

If DCount("FileID", "qryVLD", "([MIRType]=""A"" OR [MIRType] = ""H"") and ([TicketNumber]=""" & tkt & """)") Then  'validate MIR Sequence number and Ticketnumber to avoid Duplication
                    txtMIRActivity.Value = srcfile & " " & "'" & MIRType & "'" & " " & tkt & " " & PNR & " " & "DUPLICATE DATA" & vbNewLine & txtMIRActivity.Value
                    Print #2, srcfile & " " & "'" & MIRType & "'" & " " & tkt & " " & PNR & " " & "DUPLICATE DATA"
                    Close #1
                    'Name SrcFolder & srcfile As dupfolder & srcfile 'Move Duplicate file
                    GoTo NextFile
                    'DCount("FileID", "qryVLD", "([MIRType]=""A or H"") and ([TicketNumber]=""" & tkt & """)")
                    ElseIf DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """)") Then  ' validate data for duplicates with MIRType and TicketNumber
                    txtMIRActivity.Value = srcfile & " " & MIRSeq & " " & tkt & " " & PNR & " " & "DUPLICATE MIR FILE" & vbNewLine & txtMIRActivity.Value
                    Print #2, srcfile & " " & MIRSeq & " " & tkt & " " & PNR & " " & "DUPLICATE MIR FILE"
                    Close #1
                    'Name SrcFolder & srcfile As dupfolder & srcfile 'Move Duplicate file
                    GoTo NextFile
                    End If

 

[namliam]

Ah I see the problem, it isnt the dcount, .... there is no = 0 or > 1 or anything behind it in the where....

READABLE CODE is maintainable code !!!!

If DCount("FileID", "qryVLD", "([MIRType] = ""A"" " & _ 
                           " OR [MIRType] = ""H"") " & _ 
                            and ([TicketNumber]=""" & tkt & """)") [COLOR="Red"]> 0[/COLOR] Then

 

[anishkgt]

Well that did help for a while it missed out one file with MIRType V and this code solved it

DCount("FileID", "qryVLD", "([MIRType]=""A"" OR [MIRType] = ""H"") 
                                                         and ([mirtype]=""" & MIRType & """ 
                                                         and  [TicketNumber]=""" & tkt & """)")

 

[anishkgt]

just noticed that the CounterA04 is omiting the first line, so when there is only one A04 line the value becomes 0 else if there are 2 A04 lines it is 1. How can i change it to read the first line also

 

[namliam]

well the counter is probably starting at 0, you can try starting it at 1
CounterA04 = 1
and increment for each line?

What sence is that check? Mirtype = A or H and Mirtype = Mirtype??? doesnt really make much sence to me... but its your logic

 

[anishkgt]

seem to have solved it