Hello,
I thought this would be easier, but the results I'm getting are inaccurate.
I have a table that includes items and dates. There are multiple records per item. I want to show each item only once with the earliest corresponding date. I've sorted, used the TOP, DISTINCT, and FIRST functions and can not seem to come up with the right combination.
The query is already works from another query that sorts by item and date. I just need to pull the first record for each. I would appreciate any suggestions!
I have written a simple module that sorts and compares two strings. Next I am trying to create an event procedure for a button that will send all the values from three selected colums to that function for comparison.
Function Check(sImp As String, sAns As String, iOpt As Integer) As Integer
Dim lImp As Long 'Imported values (unsorted) Dim lAns As Long 'Entered values (unsorted) Dim lAcnt As Long Dim lCcnt As Long Dim sAnswer As String 'Correct Dim iMini As Integer Dim sImpSort As String Dim sAnsSort As String
For lAcnt = 1 To lImp sAnswer = Mid(sImpSort, lAcnt, 1)
' option Select Case iOpt ' all Case 1 Check = 1 Exit Function
' or Case 2 For lCcnt = 1 To lAns If Mid(sAnsSort, lCcnt, 1) = sAnswer Then Check = 1 Exit Function End If Next
' and Case 3 If sImpSort = sAnsSort Then Check = 1 Exit Function End If Case Else End Select
Next
End Function
What would be the best way to send these values to the function? loop through a recordset? (Note: Not sure how many records the DB will eventually store. Lets just say 10,000 for now)
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
im working on an access report and would like a little vba script to run on when the page prints out the next record (mostly just need to hide/unhide a few objects and change the value of a variable on the report when it prints the next record)how i can trigger this (as there seems to be no onNextRecord event handler)
edit: upon a better inspection of the reports record source i realized that on next record doesnt actually work (i could force it to by adding a few new fields to queries and forcing endless loops), what i needs actually a lil more complicated
my report has a header, in the header is a field called productionNo, the record source has multiple instances of prodno's with countless duplicates, what i need is a function to trigger when the productionno changes, there will probably be a few pages with the same production number (theyd be grouped together though), so it should only trigger when the value changes, not when it stays the same
I modified a form and now it seems it's impossible to modify/add records. It seems that all comes from when I modified my record source query to add a a field in which it COUNTs all related records in another table. Now the form can't modify anything else but I need to change other fields or add new records! In other words: how can I add a simple box which, for every record of my table/form, counts the number of related records in another table (relationship is 1-to-many, and of course is based on a field in table2) letting me modify and add records? Thank you
I'm an expert user in Excel and am trying to learn more about Access, so I relate many of the functions to what they are called in Excel. And what I'm trying to do right now is count the number of fields in a specific record where the value is a specific result (CountIf).
For example, I need to return the number of fields in a specific record where the value is "Meets Requirements". The specific record will be determined by the Name, Contact, and Email fields (which together comprise the Primary Key).
Is there a way to count the values in multiple fields that equal "Meets Requirements"?
This actually works, but every time the print preview is closed, it crashes Access. I have researched this particular issue, and some of the solutions I have read and tried lead to "that method is not allowed or supported" errors or Run-Time 438 errors.
Is it possible on the Find Record button added to a form to adjust the search function so it defaults to a specific box on the form? I have a form for tracking employee's and on my Find Record button I would like it to default to the Last name instead of the Record number.
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields. 160037 213137 224356 235716 235800 12341 21708 22732 Here is the date function we use: Function f2Date(strDateOld As String) Dim strDate As String, strMonth As String, strYear As String strMonth = Mid(strDateOld, 5, 2) strDate = Right(strDateOld, 2) strYear = Left(strDateOld, 4) f2Date = strMonth & "-" & strDate & "-" & strYear f2Date = CDate(f2Date) f2Date = Format(f2Date, "mmmm d yyyy") End Function
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Is there any function that can convert either a True, False, "Yes", "No" or Null value.... to a yes/no (check box) data type?
What I want to do is create a make table query. I would like to add a checkbox field from the query. So if there was a function that could convert a data type to a Yes/No checkbox, this would be very helpful...
To give you and idea of what I'm talking about...and how I want to use this... If I am making a table from a query and I want to add a number field, but leave it empty for the time being, what I can do is make a new field in the query maybe call it "Number1". What I would do is Add this as a field in my make table query:
Number1: Cint("")
This will add a blank field that is formatted as a number field when I run the make table query.
So using the same logic, I'd like to make a blank (or unchecked) Yes/No check box field when I run my make table query.
Anyway...that's what I'm hoping for.... I hope someone can help!
I'm having a problem getting a form to open. I'm using access 97 on on XP machine and whan I open my database I get Function isn't available in expressions in query expression '[Case Types]![theName]&"-"&Format([SourceDocs]![CaseNumber]&"","00")&"-"&Format([SourceDocs]![CaseYear]&"","00")'.
This database works fine on a Windows 2000 machine running Access 97 but none of the XP machines will open it.
I have a database in which I am designing a report which includes a name/address block. I am surprised to find that the report does not recognise the Chr() function in the text box control source string "=[Organisation] & Chr(13) & Chr(10) & [Address]..."; designed to insert a CRLF between the contents of the named fields.
I know the syntax is correct because I've checked by using the expression builder to replicate the code. (Expression builder includes the Chr() function, as expected.)
The interesting thing is that the function is recognised in other databases used on the same machine, both those created remotely and locally.
I am using a crosstab query to gather info from a table. I am using the Format [DateAppt], "mmm". The query ran successfully before but I now get the error "Function is not abailable in expression in query expression 'Format ([DateAppt], "mmm")'. This is used for column headings in a report that gives results by month.
I am using Microsft Office 2000 Premium with XP Professional operating system.
I used the original disk to repair Office thinking that the function was missing from the function library but to no avail.
Just wonder if anyone could give me some help on an IIF function used in my query:
I have a table which has numerous “W” codes, the charging for an order does not occur if it has a W12 or a W14 code and has no “[Authorising Name]” BUT does however charge if the weight of the order is over 100kg
IIf([WCode]="W12" Or [WCode]="W14",IIf([Authorising Name]="",0,[charge], IFF ([weight]>99,[charge],0)))
I can get this working for a single W code but not for the 2 OR I can get it working for both Codes but then the weight side doesn’t.
I have tried all combinations of <> = < etc etc but still no luck., I think I need another OR but no matter where I put it I hit problems either with the function or the result.
Another small issue, my [authorising Name] field can appear to be empty, but in the field are spaces what is the best way for ensuring I capture both empty and theses space records ?
I have a table that contains a year (school year) and an End Status Code, I need to flag students that have the end status code of 45 in school year 2005.
When I use the below code the column popluates with "#ERROR".
IIf([ST010]![SCH_YR]="2005" And [ST010]![END_STAT]="45","Y","N")
hi there am new to access and i want to know how to use the function now in a query like i want to know all the birthdays in the date base by using the function now.. firstly i did date part for the day and the month,, then in the criteria box of the day i inserted the function now () but dont know what to put.. any1 can tell me what to do pleaseeeeeeeeee ???????/:) :) :)
hi there am new to access and i want to know how to use the function now in a query like i want to know all the birthdays in the date base by using the function now.. firstly i did date part for the day and the month,, then in the criteria box of the day i inserted the function now () but dont know what to put.. any1 can tell me what to do pleaseeeeeeeeee ???????/:) :) :)
Basically if i have say Column A that has some fields filled in and Column B with other fields filled in. I want to Populate a column C with either the stuff in A or B.
So If Column A is blank and Column B is blank, then i want Column C to stay blank.
If Column A is populated and Column B is blank, then i want Column C to be populated with what column A has.
If Column A is blank and B is populated, then i want Column C to be populated with what Column B has.
I have parts table in Access where a part id is automatically assigned to each part number. I am trying to get the last part number entered in the parts table by querying on the max part id. The max part id will always be the last part number entered in the table. I don’t want to display the part id though; I only want to display the part number associated with that max part id. This is what I have been playing with.
"select part_no, (max)part_id as part from tblPartsSignOut "
Can someone give me some insight on how to structure this query the correct way? Thanks
