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
The scenario is... also apologies if this seems a lot. Simple really.
An edit button on a subform is to open a separate form for editing, and I obviously want to apply a where condition to display the relevant record. So I have
DoCmd.OpenForm "editReviewPEST", acNormal, , ReviewID=Me.ReviewID, acFormEdit, acWindowNormal, False
The Record Source for editReviewPEST uses an INNER JOIN...
SELECT Reviews.Year, Reviews.ReviewID, Reviews.LesseeID, Lessee.DateOfOrigSanction, Reviews.Exposure, Reviews.ExpoCurrency, Reviews.SentToCredit, Reviews.Approved, Reviews.Completed, Reviews.Comments, Reviews.LevelOfLastSanction, Reviews.CreditManager, Reviews.TypeOfReview, Reviews.DateOfLastCreditReview, Reviews.DateOfNextCreditReview FROM Lessee INNER JOIN Reviews ON Lessee.LesseeID = Reviews.LesseeID WHERE (((Reviews.LesseeID)=[Forms]![Reviews]![SelectLessee])) ORDER BY Reviews.ReviewID DESC;
So I get the form to open with all the records relvant to the lessee, but to filter it further to exact ReviewID, I thought I could include ReviewID=Me.ReviewID (an Autonumber PK) as the Where Condition to the DoCmd.OpenForm. Instead I get a form which looks like Add Record Form.
I have a Datasheet form which has a text field "Customer" which is hyperlinked to open another form to populate Customer details. I am using MacroBuilder with the OpenForm command with the Where condition which is something like this
WHERE ="[Customer]=" &[Customer]
But this does not seem to work for this field.
[The Customer field contains names that have text and also other special characters. Ex: "John Chemicals (U.S)"
I am rewriting an old Access 2003 database in Access 2010. When creating new command buttons, the current theme gives them a default appearance. I need to apply this appearance to old command buttons. I know there is a way to select the default button and apply its properties to others quickly. I have done it before but didn't write the process down .
View 2 Replies View RelatedGood 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.
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.
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?
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.
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..
TQ
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.
I need to count the number of cells that match a certain number like >0 and <60. Thanks!,
Mike :confused:
Hi there, I currently have the below tables:
tbl_stores
ID NAME
123 Store A
456 Store B
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
Any ideas? Thanks for your help with this one!
Cheers
Tony
Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?
View 7 Replies View RelatedI have written some code that adds the address to the box dependant on base, but i get the error message
Compile error:
end if without block if.
Private Sub Report_Open(Cancel As Integer)
If Me.Base = "STN" Then Me.Address = "address here"
End If
End Sub
Can anyone explain this for me
I have searched, but I cant find a thread for this.
I have a calendar that I input when people are going to be off. If there is nothing for that date then the record is blank. I have a form that opens when the db opens attached to the date on the switchboard. If no one is off then the form opens blank because there is no date in the table to match. What I want to do is have a something built into the code that says if there is no match (null??) then dont open the form.
DoCmd.OpenForm "PopUpCalendar", acNormal, , "[Calendar]![Date] = Forms![Switchboard]![MainDate]"
Hi, im very new to visual basic programming...what i am trying to do is implement an If Statment on AfterUpdate for my form:
If record_is_new Then
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()
Set RS = db.OpenRecordset("LTOT_second_oxygen", dbOpenDynaset)
RS.AddNew
RS![HospitalNumber] = Me![general_info.HospitalNumber]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
.........more of this kind of code
Else nothing
End If
any ideas on the vb code that would do this?
I’ve created a report containing two labels and two text boxes. The “Record Source” for the report is Tbl_MasterList2. The control source for both text boxes is named field4 and field5, which are based in Tbl_MasterList2. As a result, my report displays all records from Tbl_MasterList2, field4 & field5 including those, which are blank.
How do I write an expression in my report that will allow me to display the values in field4 if filed2 = stringXYZ?