Here is what I have:
Code:
ActiveSheet.Range("$A$1:$BU$87").AutoFilter Field:=17, Criteria1:="="
ActiveSheet.Range("$A$1:$BU$87").AutoFilter Field:=1,
lilp32
Joined: Oct 1 '10
Location:
-
Help with counting visible rows after filter in excel
I am trying to set up a prompt to print only if filtered rows are visible. I need to count the visible rows after the filter is applied (not including the header). I have tried various combinations but nothing seems to work every time.
Here is what I have:
... -
Thank you - that makes sense. I will try it and see how it goes.
For the second question, I connected the access table through the "get external data" menu in Excel, so I think is uses OLE DB. The access table is located on the "Study Numbers" worksheet. I use the formula: strFormula = "=IFERROR(VLOOK UP('MRSA'!RC[2],'Study numbers'!C:C[1],2,FALSE),"""") ". -
Lookup field linked to access database not refreshing
I have a program that contains a lookup formula that references data linked to an Access database.
I have "ActiveWorkbook .RefreshAll" at the beginning of the program but sometimes the workbook does not refresh. Any suggestions?
Also, is there VBA code I can use to cross reference instead of putting a lookup formula in every cell? -
It's been awhile, but I had to fix errors in the dataset. I am now back to trying to delete the records. I am having trouble selecting the records I want to delete. I have the fields ID, ADMITDATE, CultureDate and POS_ADMIT. Only those who have POS_ADMIT=1 have a CultureDate. I would like to eliminate all visits after the first CultureDate. I tried to make a select query as mentioned by NeoPa above but I need more guidance on how to join the...Leave a comment:
-
Strange, but changing the format to 24-hours seems to have worked!
Thanks.Leave a comment:
-
Between dates query not working
I cannot figure out what I am doing wrong. I am trying to write a query to put a "1" UNDER "POS_OTHER" when a culture (CULTUREDATE) is between [ADMIT_DATE] AND [DC_DATE]. All 3 date fields are formatted as vb general date/time, but there are many instances being missed (see screenshot).
...Code:SELECT Neg_on_admit_dates_formatted.REPID, Neg_on_admit_dates_formatted.ADMIT_DATE, Neg_on_admit_dates_formatted.DC_DATE, Neg_on_admit_dates_formatted.CULTUREDATE,
-
I'm afraid I'm not getting this right. Here is what I have, and it returns no rows:
Code:DELETE Table1.*, Table1_1.ADMIT_ESBL_SURV_POS, Table1_1.ADMIT_CUL_DATETIME FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.ID = Table1_1.ID WHERE (((Table1_1.ADMIT_ESBL_SURV_POS)=1) AND ((Table1_1.ADMIT_CUL_DATETIME)<[Table1].[ADMIT_CUL_DATETIME]));
Leave a comment:
-
Need help selecting and deleting table rows based on criteria
I am working with a table in Access 2010 which includes test result (0/1) and date of result by subject ID in addition to a number of other fields.
I would like to include all results up until and including the first positive result, but eliminate all subsequent results.
I am not sure about the best way to go about this, so any suggestions would be appreciated.
Example:
Subject 123 has negative... -
Now my problem is that the date is not being inserted correctly. I've tried changing the default date to Format(Now(), "m/d/yyyy") or removing it altogether but the date is still being inserted as 12/30/1899 or 12:00:09 AM.Leave a comment:
-
I got it to work!!!
...Code:Dim i As Integer Dim CollectionDate As Date Dim StudyDay As Integer Dim ID As Integer CollectionDate = InputBox("Enter date", "Date", Date) StudyDay = InputBox("Enter study day") ID = InputBox("Patient Number") For i = 1 To 10 DoCmd.SetWarnings false DoCmd.RunSQL "INSERT INTO tblSamplesLeave a comment:
-
Thanks, but I think I am missing how to insert the variables through the SQL query.
...Code:Dim i As Integer Dim CollectionDate As Date Dim StudyDay As Integer Dim ID As Integer CollectionDate = InputBox("Enter date", "Date", Date) StudyDay = InputBox("Enter study day") ID = InputBox("Patient Number") For i = 1 To 10Leave a comment:
-
How do I add consecutively numbered records into a table?
I am trying to add records into an Access 2010 table. Each time, I need to add 10 records (numbered 1-10). All other fields are the same for all 10 records.
The fields I am using are: SampleNumber (autonumber), SampleSite (always numbered 1-10), CollectionDate (date), StudyDay (number), ID (number).
For example, on CollectionDate 3/14/13, we collect 10 samples for ID 20. All samples have the same CollectionDate,... -
Interesting, when I commented out the print communication steps I lost the "fit to one page" settings on both sheets but maintained the other settings.Leave a comment:
-
Thanks, I was able to get this working by creating a second query with the distinct count and joining it as Rabbit suggested. I am still trying to understand subqueries. Thanks again.Leave a comment:
-
I've added save points in multiple places including Line 87, 158, etc. and still no success.Leave a comment:
-
How can I sum the same column twice but have one count only unique values?
I have a table in an Access 2010 database. The table contains the fields Subject and Test_Number. Each subject and test number are listed in one row. Subjects can have multiple tests so there are duplicates in the Subject column. Tests can have multiple results, so there are duplicates in the Test_Number field. I would like to end up with one row per subject with Test_Number counted as follows:
1. "Results" - the total... -
I am using Windows 7 and Excel 2010.Leave a comment:
-
Thank you for the suggestions. I tried both and neither was successful. It is frustrating because it works fine if I go step by step.Leave a comment:
-
Help with Excel VBA not saving print settings on second worksheet
I have an excel program that is set to create a workbook, import data into 2 worksheets and reformat the sheets for printing. I need to format the print settings for both worksheets but the print settings on the second worksheet are not being saved. The program works fine when I go step by step but when I close the workbook and open it back up, the second worksheet has lost its print settings. I am including the entire program but step 17 is what... -
Thanks, and yes I am not sure why there is an extra quotation mark in the middle. I see that the suggestion by zmbd is in a different order and has an additional parameter.
You mentioned a quick and straightforward alternative - do you mean removing the password or is there something else I could do? I don't really consider this a specific issue? This seems like an issue many people would have as many databases are password prot...Leave a comment:
No activity results to display
Leave a comment: