Hi all,
I had a little problem with delete query. I would like to delete the records by checking two fields if both fields are empty. I mean if a records had both these fields are empty only. If one of the field has data , the record should stay there.. Is there any way I can do this?
Thanks in advance.
Thanks
If I have about 9 Text fields, How would I go about checking to see if all of the text fields are empty, then hide all the fields, but if any of them have anything in it, show them all. Here is my code, but it doesn't work:
Code:If SpecAgent = "" And SpecArea = "" And SpecBenefit = "" And SpecCompany = "" And SpecCSR = "" And SpecDoctor = "" And SpecHospital = "" And SpecPlan = "" And SpecRx = "" ThentglNo = TruetglYes = FalselblSpecAgent.Visible = FalseSpecAgent.Visible = FalselblSpecArea.Visible = FalseSpecArea.Visible = FalselblSpecBenefit.Visible = FalseSpecBenefit.Visible = FalselblSpecCompany.Visible = FalseSpecCompany.Visible = FalselblSpecCSR.Visible = FalseSpecCSR.Visible = FalselblSpecDoctor.Visible = FalseSpecDoctor.Visible = FalselblSpecHospital.Visible = FalseSpecHospital.Visible = FalselblSpecPlan.Visible = FalseSpecPlan.Visible = FalselblSpecRx.Visible = FalseSpecRx.Visible = FalseElsetglNo = FalsetglYes = TruelblSpecAgent.Visible = TrueSpecAgent.Visible = TruelblSpecArea.Visible = TrueSpecArea.Visible = TruelblSpecBenefit.Visible = TrueSpecBenefit.Visible = TruelblSpecCompany.Visible = TrueSpecCompany.Visible = TruelblSpecCSR.Visible = TrueSpecCSR.Visible = TruelblSpecDoctor.Visible = TrueSpecDoctor.Visible = TruelblSpecHospital.Visible = TrueSpecHospital.Visible = TruelblSpecPlan.Visible = TrueSpecPlan.Visible = TruelblSpecRx.Visible = TrueSpecRx.Visible = TrueEnd IfEnd Sub
I have a form with mandatory fields highlighted a different colour (yellow or blue). On the form are 3 tab pages with subforms which also have these fields.
I have added a checkbox named incomplete to each tab page and to the form. My intention is to try to write some code to look at each field on the page and if the non-white (ie mandatory) fields all have a value, then the incomplete on that page is changed to no. The code then checks the incomplete value for all 3 pages - if these are all no and the form's mandatory fields are also filled in, then this also becomes no.
When creating reports, I can then find out which records have not been completed and notify the relevant staff. Also if the data is incomplete, they don't want those records appearing in reports - so I can use the incomplete value from the form.
For my query I have 3 tables.
TEAMS. This contains TeamID / TeamName and TeamManagerName.
TeamAPPLinks. This is a many to many relationship and contains TeamAppID / AppID and TeamID (TeamID links to Teams.TeamID and AppID links to Applications/AppIS)
Applications. This contains AppID / AppName / Pass/Fail.
So teams can have many applications and applications can have many teams.
I only want to show teams that have all applications that have the Pass/Fail column as PASS. I have managed to do this for FAIL runing a query in a query but obviously its different for PASS as all associated applications have to be a PASS, not just one FAIL for a FAIL.
Hi all. I want to check for not null value constraint for all tables in database . I tried the following but when i run it i get the following error:
Compile error:
Invalid Next control variable reference
I be happy if some one one help me fix this error.if i remove the bold lines the program workes well but it does not put not null for table fileds that requries value.
picture of the output without the bold part
http://i5.photobucket.com/albums/y1...007/notnull.jpg
Code:Option Compare Database''This module displays field name and type in a massage boxFunction ShowFields(pTable As String) As StringDim db As DatabaseDim rs As RecordsetDim I As Integer''Dim j As IntegerDim n As Integer''Dim NL As StringDim strHold As String, ST As String''Dim x As Variant''NL = Chr(13) & Chr(10) ' Define newline.Set db = CurrentDbSet rs = db.OpenRecordset(pTable)n = rs.Fields.CountReDim x(n - 1, 1) As String'''ST = "Create Table " & pTable & vbCrLf''adding Create table and table name to statementST = "Create Table " & pTable & vbCrLf & "("For I = 0 To (n - 1)For Each fld In tbl.Fields ST = ST & rs.Fields(I).Name & " " & FieldType(rs.Fields(I).Type) & "," & vbCrLf If fld.Required = True Then ST = ST & " NOT NULL" & " " Else ST = ST End IfNext Irs.Closedb.CloseSet db = NothingShowFields = ST '' returns the fields name to main functionEnd Function
on click even code
Code: For Each T In db.TableDefs '''Skip the system tables If Left(T.Name, 4) <> "MSys" Then ''' this line determines the primary key of the table ''' by calling GetPk function from module pk = Left(GetPK(T, db), InStr(1, GetPK(T, db), "<-") - 1) cont = cont & ShowFields(T.Name) & vbCrLf & " primary key " & "(" & pk & ")" & vbCrLf & ")" & vbCrLf End If Next T ''' This line of code post the generated table statment to a massage box MsgBox cont
I have a transaction form and there is at least two fields I need to make sure have been entered before the record is saved.
I have no problem with text or numeric fields but I can't seem to be able to check the contents of a drop down field.
What is the best method to use to make sure a drop down has been selected by the user and contains a value.
I have temporarily used a default value in the drop down but that's not really what I want.
how can i disable a textfield or two in a form when the textbox is unchecked also how do i add a default value for it while the textbox itself is disabled, can i get away with it by adding a default value on the textbox?
View 14 Replies View RelatedHi,
simple question. i have an unbound textbox on a form that has its source as
=[field1]/100*17.5
when field1 is empty on loading, the textbox shows nothing.
when field1 has a value entered, it shows the value
when field1 is reset, the textbox shows #Error
( field1 is cleared by code - me![field1].value = "" )
how do i suppress this to show nothing? i have tried
=isnull([field1]="","",([Field1]/100*17.5) &
=isnull([field1]/100*17.5)
im not great with sql statements ( if thats what you call them )
any advice would be great.
nigel
I am trying to insert a value in a field from an existing fields. I attached a sample of my database to show the query. In the NewProduct field I need "change of product" data to override the "product" data but if "change of product" is null/empty the "product" value should be in the NewProduct field. In other words the NewProduct field must take the value of either/or of the change of product or the product field. If
This is what I have so far:
NewProduct: IIf([Change to Product]<>[Product],[Change to Product],"")
Thanks in advance.
I am trying to create a multiple IIF statement with IIF(ISNULL([xxx]) and an or and it does not work any help would be appreciated.
See below
IIf(IsNull([AI Rate]) or IIf([AI Rate]>0),0,1)
I am trying to figure out a formula using the iif and isnull function.
I am doing an on time report that looks at the Date Received, Date Required, Ship Date, Todays date and a few other things.
The formula I am trying to work with is
DAYS WORK DONE IN: IIf(IsNull([SHIP_DATE]),[TODAY]-[REQUIRED]+1,[SHIP_DATE]-[RECEIVED]+1)
What I am trying to get it to do is if the ship is empty subtract the todays date from the required date and add 1. If the ship date has a date subtract the ship date from the received date and add 1.
When I run the query I just get #Error.
Is there an easier way to do this or do I just have an error in my code?
Thanks for your help
Mike
I've been searching the forum for a while, so hopefully I haven't missed the answer to this one. It may sound like a small problem, but it's driving me mad.
I have a series of TextBoxes on a Form.
Two of these are named 'Department' and 'Shift'.
Originally, the ControlSource for the former was 'Department' and for the latter was 'Shift', both from the same source table.
All data was being displayed without any problems (any null values resulting in an empty field).
I altered the ControlSource on each to read:
=IIf(IsNull([Department]),"Unknown",[Department])
and
=IIf(IsNull([Shift]),"Unknown",[Shift])
The Shift field works as I'd hoped it would, but the Department field displays '#Error', whether there is a value or not.
I've tried deleting the problematic TextBox, then recreating it by copying and pasting from one that works (in case there was some setting I was missing). In this case, the only difference between the two is the source field, but given that using the source on it's own doesn't present any problems, I'm at a loss.
This same problem occurs on a small number of other Forms, as well as on certain Reports.
Any thoughts welcome.
Hi guys,
I have a mod called EntryAge. It requires two arguments. A start date and an End date.
I'm trying to use it in a Query, but I am running into problems because there are times when the Start date and the end date are both Blank.
I've tried this nested statement and it keeps telling me. Contains invalid syntax or I need to enclose it in quotes.
I've copy and pasted my code below. Can someone please help me figure out what is wrong with this silly thing.
intSecondaryTime: EntryAge(IIF(ISNull([dtmSecondaryReceived]),0,[dtmSecondaryReceived])),(IIF(ISNull([dtmSecondaryComplete]),0,[dtmSecondaryComplete])))
What I am trying to say is if the received date is null make it 0 or use the date given, and if the complete date is null use 0 or use the complete date.
It then will perform the calculation that the Entry Date mod does.
If you want I can copy and past the Entry Date Mod. If that will help.
I have a query and it can be a null value if it is null I want it to return 1 if not null I want the calculation. This is what I have but it will not work.
Please help!!!
repo on sitePercent of copy jobs not rejected:IIF([Rejected Job Percentage]=0,1,(1-[Rejected Job Percentage])
Basically i have a drop down that i want controlling the Criteria for each field in my query to have an advanced search. If i leave a field blank the query comes up with nothing so i tried this:IIf(IsNull([Forms]![Inventory Report Search]![Model]),Is Null,[Forms]![Inventory Report Search]![Model])this is not working. it is still returning the query blank. if i put valid critera in the dropdown it comes back Fine.how do i make is so if a field is blank it will return it as null or not even there.Note: also tried this;Forms]![Inventory Report Search]![Model] Or Forms]![Inventory Report Search]![Model] Is Nullworks, But comes back as too complex after a few searches and when i open the query there is a million or's in there. so that wont work.
View 3 Replies View RelatedThe below SQL gets it's criteria from a form but if the form has no Date1 and Date2 values I would like it to return all records.
I could not tinker it into submission. :confused:
Any pointers would be great.
SELECT [Central Western Region Database].[Date of Term], [Central Western Region Database].[Agent Number], [Central Western Region Database].[First Name], [Central Western Region Database].[Last Name], [Central Western Region Database].[District Name]
FROM [Central Western Region Database]
WHERE ((([Central Western Region Database].[Date of Term]) Between [Forms]![Compliance Lair Reporting]![Date1] And [Forms]![Compliance Lair Reporting]![Date2]) AND (([Central Western Region Database].Status)="inactive") AND (([Central Western Region Database].[Employee Type])="agent"))
ORDER BY [Central Western Region Database].[Date of Term];
I am using a frame on form to get report. Below is the code used to filter report.
Select Case Me.fraReportType.Value
Case 1
strReportType = "Like '*'"
Case 2
strReportType = " Is Null"
Case 3
strReportType = " Is Not Null"
End Select
The second and Third case is working fine. While the first condition is not working. This filter is on date field. There are three possibilities:
1. If we need all data
2. If we need is null data
3. If we need is not null data
How I can get the first condition using my code mentioned above.
All using access 2010. I have a query1, query2 and query3. Query1 is my master. Query2 and Query3 was created based on different criteria but derived from the Query1. I now want to exclude the records from Query1 that are in Query2 and Query3. When I try to put isnull in criteria of both queries Im trying to exclude; instead of returning the remainder records in the master I get none.
View 3 Replies View RelatedI am using the QBE grid and am writing a select query to select only records with an empty Date Closed Field. The Date Closed field is a Date/Time Field. I am using Access 2003. When I use in the criteria IsNull([DateClosed]) I do not get any records selected which have an empty Date Closed field, have I a bug? and if so please could anyone point me in the right direction.
View 4 Replies View RelatedI have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?
For example (these are my combo box titles in order):
Product
Type
Customer
Contract #
I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.
I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly.
It should be exceedingly simple, but I got mashed potatoes between the ears today.
I make a query looking up for Name+Firstname+Ref number which I display in a tabular form .
Now, I want to use check boxes to select the ones I wants to print. But the thing is, if I add a check box in the main part of the form, it'll be the same box for all records. Not what I want.
So, how to create (add) a column so I can check individual records in the form?.
Thanks
Hi guys, a company in my city is giving out prizes, and for each buy you get a sheet with 25 numbers ( kind of bingo thing). Well I have like 300 sheets . So i did an Access where I enter the SheetID and the 25 numbers. Now I have to do another table where I can insert the number that will be draw and whenever one of the sheet is complete, it let me know that I win!!! and the SheetID.
Any ideas!!! please please
Hi,
i want to set values (=numbers) in fields of a table by selecting check-boxes in a form (based on the same table).
as checkbox1 =1
checkbox2= 2
etc
If the box is checked the value should be entered, if unchecked the value should be removed.
Afterwards i want to show the sum of these values in another field in the same form.
I suppose this requires VBA. Could anybody help me plz ?
Thx
I've had a search in the threaqds for an answer but cannot find anything specific, plus this one has stumped me.
What I need to do is check if a certain field has a value if another field has a specific value ??!!?
Ok so if Combo Box 1 is set to 1, then Combo box 2 must have a value selected.
If ComboBox 1 is set to 2, then Combo Box 3 must have a value.
for any other value for ComboBox 1 box 2 & 3 do not require a value.
If either of these statements are not correct then a message box should come up
Any help would be greatly appreciated
MattP
I have a form where I enter student names, and it often happends that I enter the same student a couple times, Is there any way to let the user know that there is allready someone in the system with the same name, like a pop up message.:confused:
thx
hello all,
I have no idea how to run error checking
I get this error
can't append all the records in the append query.
i want a custom error to come up instead of that.
PLEASE HELP.