Can Access Expression Builder mirroring "IF function" from Excel

Hi David,

I am stuck with the code.
Please find the attached pdf.
I can't make it work. Could you please give me a feed back?

Thanks and have a good weekend !! :)
What error message does it given when it stops?

What happens when you hover over the CustID and rs!CustID in that screen? Do the values there make sense? Also hover the variable a just for fun...
 
Crazy Monday before holiday. So much enjoyment in these upcoming holiday!!!

@ CJ_London. The sql seems work. I tried it and the first couple hundred rows populate correctly but now it seems freeze and it’s been like that for almost an hour. But I’ll let it run overnight to see how it works. Thank you so much for gave it a try and I’ll post the update tomorrow. :)

@ David. I can’t thank enough too to help me out. I’m almost desperate when can’t solve it and your reply has inspired me to keep playing with the queries. But I haven’t have a chance to look deep into it again. I’ll post the update tomorrow. :D
 
did you index as suggested? If the table has 1m lines, then it will get very slow without it. In similar circumstances I have improved performance can from 'overnight' to about 30 minutes when indexing is added.

Other suggestion is to split the table into two or more sections - just make sure you split on a customerid line
 
CJ, I did not put index in the query. Maybe that's why it takes so long.
Could you please tell me how to insert the index query into the last query that you sent?
Many thanks!!
 
CJ and David, wish you have a Merry Christmas and Happy New Year!!!

I'm taking vacation and won't be back till mid January 2014.

Many thanks to all of your help guys. This forum is rock!! :)
 
For when you get back
how to insert the index query
You don't do it in the query but in your table design - go to design, select the field and set the index to indexed - duplicates OK.

If you are trying to do this from a linked table, import the data into your db - as per your example - with the ID column being an autonumber
 
you can also cut down the table size and improve performance by deleting the blank records or if the record is just a header

The query would look like this:

Code:
DELETE [Text File].*
FROM [Text File]
WHERE[Text File].CustID Is Null Or [Text File].CustID="transaction"
 

Users who are viewing this thread

Back
Top Bottom