Search results

Hi all, I have an Excel macro to do the following tasks: A. Open a file from local drive. B. Then copy one of the tab to B file. C. Repeat the same process 16 times to complete copying and pasting to B file. The macro runs fine except I would need to click "Do not update" option whenever any...

It is my habit always use "Like" instead of "=" when it should be "=". Thanks for pointing that, I will change to "=" to have less execution time.

Another question though. The module is working now. Then how can I include it in one of the steps in Macro... I am asking this since there are some queries have to be run first before the module. Then there are some queries have to be run after the actions in the module. I would like to...

I think I figured out why it was not working. I should have one criteria one line to make it work: Was: sSql = sSql & " WHERE (([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7276001"" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7926031"" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like...

Thanks for the comments. The data by 12 month columns is downloaded from the SAP. Probably I didn't explain it clearly that the "Report date" column is actually the SAP date to run the data I added when an Excel macro is executed to format the SAP data like a table to feed into the Access...

I put the SQL codes in a module but it didn't fly. May be I should better to use recordset to code in the module: the Access Table's name, the criteria ( 12 If-else-then?) then to update the corresponding month column?

The Access table has 12 month columns: Oct, Nov, Dec., ect. and a "Report Date" column along with other text columns. What the queries are doing: If Current Report Date = 11 (Nov) and other criteria are met, then update the Nov column to $0 and leave other month columns unchange. If...

Hi, I have 12 queries to update an Access table's one field based on 4 criteria. So basically, it is one month one query. Is it a way that I can change the 12 queries' codes to become just one module? Below is the first and second query's SQL code: 1st query: UPDATE [COST ACTUAL LOCAL...

Thanks all for your adivses. I used both ways and now my data is down to 2 decimal places.

Hi, I have about ten append queries to group various financial data to one main table. I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places. What is the...

It worked. Thanks very much!

Hi I wanted to create a simple start up screen so user is required to enter a password then hit Enter key. The VBA codes then determines if the entered password is matched or not. If matched, then the start up screen will be closed and the switchboard is opened. If not matched, then a message...

Hi, I created an Access report to show a query's output. The Access report has more than 80 lines and it shows them in all one page with no page break. I send the report in HTML format to Lotus Notes via the Access command called EmailDatabaseObject. However, when I open the mail in the...

The SQL code for each query is quite long since it tries to call out about 20 fields from the original table. I just came up with an idea if it is working or not: A) Create just a function named "GETFIELDS" to select fields except the critera for locations and product types. B) Then use a Click...

The query A and query B are in same layout in Excel except product type is different. The query C and query D are in same layout in Excel but different from quary A and B.

Hi, I have a 500K+ Access table which has detail information of 4 product type, tons of product names and 10 manufacturing locations. Currently, I have 40 queries to download the table by product type by locations in Excel and run a macro to execute the file downloading. I wonder if there is...

Thanks for the tips. I use the SQL view and successfully have 12 criteria on those 12 month columns in one query. Besides, I copy the SQL codes in a Word document so the end user can revise the codes easily (drop an old month and add a new month) in Word then paste to the query's SQL view...

Hi, below is the SQL codes for the 2nd query. The first query is pretty much the same except the month columns are from 005/2012 to 012/2012 (8 month columns) for March month table. The 2 queries are to catch if there is any double quote in any of the 12 month columns and not to replace the...

I have 2 queries to check if there is any "double quote" character in any of 12 month columns in a month table of 125K records. I use 2 queries since the maximun criteria in a query is only 8. So I use 8 criteria in the 1st query then use 4 criteria to check the remaining months in the 2nd...

Awesome! I added a query to use Replace function to replace the chr(10) with "" string(Replace([COMMENT],Chr(10),"")) in the comment field before download the table in CSV format. When I open the CSV format in Excel, the data looks fine without those extra lines from the Comment field's 2nd...