: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.
Pkg Qty: IIf([QTY]<=99,"10 PER",(IIf([QTY]>99,"100 PER",IIf([QTY]>999,"200 PER"))))
I am only getting the results of "10 PER" AND "100 PER". How do I get the results I want?
Good morning
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')"
qdf.SQL = strSQL
Me.lstSearchResults.RowSource = "qrysearch"
Set qdf = Nothing
Set db = Nothing
End Sub
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.
Can anyone help out with my task at hand.
Thanks everyone for your help.
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).
Any help would be appreciated. Thank you.
Hi,
I was using access 2000 to build a small office app. It will be on a network and will generally be used by only 2 or 3 people(max). I was wondering if the default jet technology would be good enough for this. If it isn't, what should I use? I looked at other options such as msde and ado but it's a bit confusing when you're new to this stuff.
Thanks,
scratch
I'm trying to create a database for a taxi-type service that runs on thursday, friday and saturday nights. My question is: Is there a way to save the database at the end of the night separate from the previous nights, and so that the database is empty for the next night, but reports can be viewed for all of the nights combined?
Any help would be greatly appreciated!
-Rusty
ok I have an access db which is all on access. the dbase has been operational now for 3 years and working well however, do to business needs and changes I'm trying to figure out a better way to collect and share data.
So the backend of the database is stored on our local office server when users log on to enter or review data they must be in the local office. We now had the need to access the database remotely from customer sites. Now the way this works is we use a local dialer to connect to our company's network and then are routed to our local server (you can imagine how slow this is) it is impossible to operate the current database this way. So I have been reading different posts and different options (front page, sql, asp etc.) What
I would like to do is convert my dbase to a program which can allow input and review of data both remotely and locally without a huge speed loss. What is the best approch for me?
thanks
jon
Hi,
I unticked all the boxes in the Tools > start up window. I closed my database then reopened it. Now, I can't put these settings back again as I can't see the Tools tab.
Any help will be very much appreciated.
B
Hi, i need to hide a report when it is opened in another function with
DoCmd.OpenReport rptName, acViewDesign,,acHidden
but it does not allow me the full set of arguments! why!?
cheers
Tania
I'm by no means an expert when it comes to using access and its many controls that are avaliable to use on forms. Thats why I've come here to seek your help.
I have a database that has been created by someone who has now left the company and it needs a little work done to it.
The ideal thing we would like to get working on form, is that we would like a number of options to be greyed out and only accessable when another option is ticked.
I'm not sure how to group these options together, nor an I sure how make them active only when an specific tick box is ticked. Any help would greatly appreciated on this matter.
Thanks for your time and patience.
Menes.
I ned to create a form that asks the user which reports they want to preview/print. I see it as a set of tick boxes with one saying all, then a print button. Im not really sure though, can someone offer some advise or maybe an example?
View 5 Replies View RelatedHi,
How do i create an option inside the form, such that when the user ticks this option, it will print the form in this particular report. If user ticks another option, it will print the form in another particular report format... Thanks...
Is there an option so that turning the mouse wheel will scroll through the screen rather than scrolling through the different records in a form? Thanks! :confused:
View 3 Replies View RelatedI am a bit of a beginner to Access 2003, and I would appreciate any help you can give me? :)
I have a drop down box on a form in Access 2003 with several options when an option is selected from the first drop down I would like a sub catagory of the selected drop down option to appear in the second drop down menu.
For example:
First drop down Hair, Clothes,
If you select Hair it then give the products relating to hair which need to be choosen in the second drop down menu.
I hope this makes sense.
I am trying to make a form that will allow me to make several selections and run a report. I want to be able to select from a list of Grade Levels, 9th, 10th, 11th and 12th. After I have picked my grade level or selected more then 1 grade level I want to be able to choose from a drop down list of school district. After I have choosen my grade level and my school district I want to run my report. How do I do that?
View 4 Replies View RelatedI am trying to create a window in access that will have about 30 different boxes that the user of the database can choose from to click on and automatically sort the information in both a Form and Sub-form, and display the information for the User. Frankly this is a little beyond my means, and I need to know where to get started. I have good knowledge of Access but I don't have much experience when it comes to programming in commands and such. Any help would be appreciated.
Mink
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.
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"
or something like this??
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
X = Form_frm_MainCurrent.EmployeeName.Value
stDocName = "rpt_All"
DoCmd.OpenReport stDocName, acPreview, , "EmployeeName = X"
anyone got any clue to help me out?
Thanks for your time
i'm making form from query. but all i need to do is calculate days worked (TimeService) either worker still working or resigned.
i want to know is it possible to make it condition like it to query? if not can u tell me the other way?
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....
cheers, dragon
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.
View 6 Replies View RelatedI 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'"
[code]....
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.