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...

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...

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...

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...

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...

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...

Hi all, I have an Access table with >100K records and it has a comment column which I joined from another small table. The small table has reviewers' comments by each part number they reveiewed which I copied and pasted from an Excel file. Somtimes, some reveiwers use the ALT+ ENTER key to...

Hi, I have a query to calculate the "initial" amount time "Discount Rate" to come up with the "Final" amount. However, the example below the Access query gives me 297132.6609 when I calculate it manualy I have 297132.6649. Since I want the numbers are correct at 4th decimal places, I wonder...

Hi I have a macro to export a query to CSV format by "ImportExportText". My original number field in the query have 4 decimal places. After the export, I see the numbers are in 2 decimal places in CSV file. Is it a way to change the query or some settings to show 4 decimal places in the...

Hi, I have an excel file of 17000 lines with part numbers and routing operation times. I wanted to have some formula to show the subtotal of a part number's operation hours after the filter instead of to show the total of all parts' operation hours. Is it a way to do that?

I have a huge text file which I opened it in a spreadsheet. The data is not in database format. I want to manupulate the data and put them in right columns. Currently, all data is in column A 1 thru A70000. I want to get the part numbers from column A in column D and the part numbers are...

I have several Access Databases. Some of them have a switchboard. Currently, I received an error when I wanted to open the switchboard. The error says "Run time error '-2147418113 (80000ffff)': Method 'Conncection' of object '_CurrentProject' failed. When I cliicked the "Debuy" button and...

I created 4 command boxes, 1 list box, 2 textboxs and 1 combo box in a form. I wanted all boxes have the same look like the command box. However, I tried different grey colors but still could match the command box's back color. Would someone know the exact back color code for a command box?

I have a simple Access module to download a query's output by BU field. There are 15 BUs so the module will create 15 excel files. It works well. But now people wanted to combine two or three BUs together in an excel file due to their relatively smaller records. Any suggestion to change the...

I have a simple VBA code to opoen a query based on an input of one "Plant number". My code is as follow: ***************************** Private Sub cmdOpenQuery_Click() 'Debug.Print Me.cboPlant <- Original code to enter a plant number cboPlant = "1100" DoCmd.OpenQuery...

Hi all, I have a database table which holds part number, its usage qty and some other stuffs. I wanted to create a selection query to have a new column to count how many parts which do have a qty <> 0. For examples, if there are 3 records having usage qty <> 0 in the table of 5 records...

Hi, I have a big table in which same product numbers may have multiple records with different reference number(s) in the table (For example, if the part has n reference numbers that it has n records in the table). I want to keep only one record for one part in the table by choosing the hgihest...

My table has a "PIR Valid-Frm" field in TEXT for data of "valid-from date" . I want to create a new column to have the data in date format so I can do some calculations. The data in "PIR Valid-Frm" field are TEXT format in MMDDYYYY (e.g. 12152009, 09102010, 03082011). The New fieldname is...

Hi, I have a super-sized Access table. I created 4 select queries to select data by individual product center from the table: 1 st query to get all data (if the data is not over 50000 lines) and the other 3 queries are to get data by product center (e.g. PL, SG) by product key (some product...