I've got a form with fields for projects, type of time (normal time/overtime) and hours.
the fields are cboProjects, cboTimeType and Hrs respectively (simplified for this forum)
I have a tbo in the footer, and I want it to sum all the hours entered where the type time is normal time. So I tried putting in the following expression into the Control Source of the control for tboHrsSum as follows:
=Sum([Hrs]) WHERE [cboTimeType]=1
1 is the ID of Normal Time.
This doesn't work though - can anyone tell me what I'm doing wrong?
Hi, I've used the following code in the Criteria line in a query (Access 2000)and the false condition doesn't seem to work. If I replace the false condition >0 with any other single value (say 1) it works fine.
I have a simple query and I am trying to total the value for the the quarter. Some of the months don't have a value so I used the NZ function IE April: NZ([Apr], 0). This put a zero value in each field so it could be added together. My Total field will only total the values if there is a entered value in each field. Example
Apr May Jun Q1 1 2 3 6 - GOOD 0 0 4 No Value - Bad
I have a table with the following 4 fields (these are the ones i'm having the issue with but there are others).
ID, Country, City, Date
I enter data into this table via a form
The ID is an auto number. The date is simply enetered.
The Country Field is a lookup (in the table itself) with the following lookup properties
Display Control: Combo Box
Row Source Type: Value List
Row Source: "England";"Spain";"France" etc
Now the problem...
I also want a lookup in the City combobox (on the form) which changes to reflect what was selected in the Country field.
E.G
If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.
I was told to use the following code in the afterupadate of "parent combobox":
Private Sub ComboCountry_AfterUpdate() Select Case Me.ComboCity Case "Test" Me.ComboCity.RowSource = "A;B;C" End Select End Sub
Ive tried it but as always i get an error when i use the country drop down...
A pop up box with:
Compile error: Method or data member not found
and visual basic opens with the top line of the code "Private Sub Country_AfterUpdate()" highlighted in yellow, and the ".RowSource =" is highlighted in blue.
These are the properties of my form
My Country Combobox is called - "Country" My City Combobox is called - "City"
I have put the above code in the after update of the "Country" combobox as below:
Private Sub Country_AfterUpdate() Select Case Me.City Case "England" Me.City.RowSource = "London;Manchester;Leeds" End Select End Sub
Anyone know where the error in the code is? Obviously i have reduced the code to include only one case.
I have created a simple query to subtract field B from field A and store this value in field C, however now I need the query to lookup the value from field C in the previous record and store it in field D in the current record. I'd appreciate your HELP
I am hoping some one can help me out with an SQL string I am having problems during to get working.
This is my code
Private Sub SearchRoleSeeking()
Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Dim strFilterSQL As String Dim txtBoxValue As String Set db = CurrentDb Set qdf = db.QueryDefs("qrySearch")
lstSearchResults = txtSearch1
If Me.txtSearch1.Value = "Temp" Then txtBoxValue = 1 End If If Me.txtSearch1.Value = "Perm" Then txtBoxValue = 2 End If If Me.txtSearch1.Value = "Temp or Perm" Then txtBoxValue = 3 End If strSQL = "SELECT tblPersonalInformation.[PersonalID],tblPersonalInformation.[Surname],tblPersonalInformation.[Forename],tblPersonalInformation.[DOB],tblPersonalInformation.[WantedRate],tblPersonalInformation.[WantedSalary],tblPersonalInformation.[Status],tblPersonalInformation.[RoleSeeking]" & _ "FROM tblPersonalInformation " & _ "WHERE tblPersonalInformation.RoleSeeking = ('txtboxvalue')"
let me try explain what I am trying to do. I have a form where personal information is entered into. It has an option group that allows 3 choices. I have made a form that I will use to search (the above code is from that.) What I am trying to do is if a person enters "Temp" into txtSearch1 then for the SQL statement to search "tblPersonalInformation.[RoleSeeking]" for the value 1. If a person enters "PERM then for the statement to use 2 as the value for txtSearch1 and so on.
Please don't criticize the naming, I've renamed to make the logic simpler to understand.
I am trying to run a query based on the value of a textbox. Right now if the text box is empty it shows all the records. This is what works: SELECT A.ID, B.Weight FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.Weight Like IIf([forms]![Form]![txtBox] & ""="","*",[forms]![Form]![txtBox])
The problem is when tbl B is blank (and only when its blank), I also want to see all records where weight is null.
Some examples of what I've tried (I have tried variations of): B.Weight Like IIf([forms]![Form]![txtBox] & ""="","* Or Is Null",[forms]![Form]![txtBox]) B.Weight IIf([forms]![Form]![txtBox] & ""="","Is Null or Like *",[forms]![Form]![txtBox])
I am not worried about the False condition, the problem is that Null is not being returned from inside an IIF(). When I set the WHERE statement to: WHERE B.Weight Is Null, it works. But if I try IIf([forms]![Form]![txtBox] & ""="",Is Null,[forms]![Form]![txtBox]). It doesnt work (Whether I put it in quotes or not).
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.
hi i was wondering if anyone can tell me a condition or a command in which a user will be able to enter only 6 digit number. like 123456. if he tries to enter 234 the database should not accept. is this possible also? any help would be great. Thanks
Please have a look at the following code (I am not good at VBA or SQL).
Me.[txtBox] = DLookup(“[SNum]”, “[Table1]”, “[RecID]) If Me.txtBox =>2 Then Condition if true Else Condition if false End If
I type a value in my [txtBox], a number. I want to evaluate if the typed value meets the condition or not. [SNum] is serially numbered and [RecID] is autonumber Primary Key. The above code is not working. How do I achieve this? Please help.
:o Please help... I am trying to get the following results. If the Qty is less than 99 bag 10 per, if the qty is between 100 and 999 bag 100 per and if the qty is greater than 999 bag 200 per.
i have a Yes/No checkbox to indicate whether a job has been done.. if it is done then i want it removed from the list.. so i think my query sql needs to be something like..
note..the bold bit is the bit i am questioning.
WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"
i have created a search form. any word the user types it searches certain details in my enquiries. in my form i have a list box that displays the enquiries sent by my query.
i have placed a Job_Booked (Yes/No field) checkbox in my enquiry table. this is because if the job is booked then i no longer want the record to appear in the enquiry list im sending to the form. ive tried this code, but even when i tick the job booked button, the enquiry is being displayed.. why is this
SELECT zEnquiries.Enquiry_Date, zEnquiries.Job_Date, zEnquiries.Job_Day, zEnquiries.Job_Time, zEnquiries.Enq_Forname, zEnquiries.Enq_Surname, zEnquiries.Pickup_Add, zEnquiries.Pickup_Post, zEnquiries.Dest_Add, zEnquiries.Dest_Post, zEnquiries.Quote, zEnquiries.Car, zEnquiries.Passengers, zEnquiries.Luggage, zEnquiries.Notes, zEnquiries.Job_Booked, * FROM zEnquiries WHERE (((zEnquiries.Enq_Forname) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Enq_Surname) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Job_Date) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Enq_Phone) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Dest_Add) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Dest_Post) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Pickup_Add) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Pickup_Post) Like '*' & Forms!Enquiry_Search!Search2 & '*')) And zEnquiries.Job_Booked=False ORDER BY zEnquiries.Enquiry_Date DESC;
I can't get this expression in Query to work! Any suggestions?
Account Type: IIf([parent account name] like “*hosp*”,"Hosp",IIf([parent account name] like "*vet*”,"Vet",IIf([parent account name] like “*dds*”,"DENT”,IIf([parent account name] like "*dmd*”,"Dentist”,IIf([parent account name] like "*pharm*”,"Pharm”,0)))))
as you can see I am doing search and identify for a new field.
I get an error message "the expression you entered contains invalid syntax"
i have a form of Employee Info, where with navigation button i move to the next employee. i have put a Command button to Preview Report on Single Employee Info that i am reading currently. So i want to Filter the Report with Single Employee Name and records so im coding on click even of the command button (see below) and its giving me null report.
im using where condition to filter the report and assign to it a variable employee name from the form. Reminding that source codes of both Form and Report is the same
Hi everyone, i have a form for bookings. People book by entering (among other stuff), the date and the booking slot.
Basically, what i need to do is have it so that only certain booking slots can be chosen on differnt days... for example, the booking slot of "Eve Extension" can only be used when the date refers to a Friday or Saturday, any other days it would not be allowed.
Is there any fool proof way of doing this, keeping in mind i dont exactly know VBA at all....
What method(s) are available to detect when a user is at a new record? I really just want to change the .text property of a combo box when the user is creating a new record. If this sounds stupid, I can explain the specifics.
I got this code to run correctly, which pulls records where they are not junk(can be seen at end of where condition), the only issue is that the "AND" does not turn blue. Is this a problem?
Code: If Not IsNull(Me.txt_last_rec_id) Then 'DoCmd.SetWarnings = False SQLText = "INSERT INTO tbl_batt_id " & _ "(record_num, bat_id, manufacture, date_code, barcode, status) " & _ "SELECT " & Me.record_num & ", bat_id , manufacture, Date_Code, barcode, Status " & _ "FROM tbl_batt_id " & _ "WHERE record_num = " & Me.txt_last_rec_id & " And [Status] <> 'Junk'"
I'm trying to put in a Where Condition for an ApplyFilter action, but it only allows up to 255 characters, I need more. I have very little experience using code!I want this to be the Where Condition:
Code: [Program Name] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Organization] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Program Type] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office City] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office Province] Like "*" & [Forms]![Program List]![Text34] & "*"
How do I make this into the Where Condition? An full example would be nice.
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
how do i make 2nd combobox list based on 1st combobox list, mean if i choose A at 1st combobox, only A1-A5 option appear, and when i select B at 1st combobox, only B1-B8 appear to select at 2nd combobox..
hi ppl, Could someone please help me with a query that I'm trying to create. I need to create a query where once the DATE is overdue/expired how can i turn it into a red colour or something just to separate it from others. e.g:
Length = 3 DateCleared = 31/01/2001 DateRenewal = 30/01/2004 --- obviously this date has expired and now i need to make it visible that it has expired. So I was thinking either turn it red.
thankyou.. :( :confused: Ps. In order to create DateRenewal I used a formula by using the Length to calculate three years from the DateCleared.
tbl_returns ID NAME RETURN 123 Store A 9999 123 Store A 8888 789 Store C 9999
I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B
My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.
SQL SELECT tbl_stores.store, tbl_stores.name FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2 GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store
