I have a form with two controls: DateFrom & DateTo
I want to filter based on those criteria but when the criteria is ommitted (NULL) I want to display all.
In case of other controls I have an SQL as follows:
... WHERE (tblClassesOffered.Course = Forms!frmClassReport.class Or Forms!frmClassReport.class Is Null)
But with the dates controls the best I could come up with is:
...Between IIf(IsNull([Forms]![frmClassReport].[DateFrom]),#1/1/1900#,[Forms]![frmClassReport].[DateFrom]) And IIf(IsNull([Forms]![frmClassReport].[DateTo]),#1/1/3000#,[Forms]![frmClassReport].[DateTo]) Or Is Null
However that solution is kind of ugly becasue instead of wild cards (I couldn't make them work) I'm using those made up dates and I also wish not to use the IIF if possible (like the first statement).
I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.
When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?
Could there be some sort of join preventing all records from being returned?
I am having problems with setting up a set of combo boxes.
What I am trying to do is if combo Productline is empty then in combo PartNumber would show all products but if combo Productline has a value selected then in the combo partnumber would only be able to select the partnumbers in that productline.
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
I learned the criteria on this forum Forms!FormName!Control or Forms!FormName!Control Is Null
It enables me to leave the text box on a search form blank to return all records. This seems to work but has a very odd behaviour.
In design view, I typed the criteria for 3 fields and saved the query, but when I reopened it, the criteria added 3 NEW COLUMNS and spread over 8 ROWS (as in the image attached.)
The SQL was even unreadable:
SELECT Table1.ID, Table1.FldA, Table1.FldB, Table1.FldC FROM Table1 WHERE (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldB)=[Forms]![Form1]![txtB]) AND ((Table1.FldC)=[Forms]![Form1]![txtC])) OR (((Table1.FldB)=[Forms]![Form1]![txtB]) AND ((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtA]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtB]) Is Null)) OR (((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtB]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldB)=[Forms]![Form1]![txtB]) AND (([Forms]![Form1]![txtC]) Is Null)) OR (((Table1.FldB)=[Forms]![Form1]![txtB]) AND (([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND (([Forms]![Form1]![txtB]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null)) OR ((([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtB]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null));
I don't know how to add the criteria for the 4th field.
All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
I had an issue with writing LIKE statements in query criteria yesterday [URL]....
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code: SELECT tblPeople.name, tblPeople.num FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name) INNER JOIN tblPets ON tblPeople.name = tblPets.name
[code]....
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
I have set up a report which prompts the user for a start and end date. However, if they want to run it for a full month they enter eg. 01/07/07 and 1/07/07 - this will not include entries ending on 31/07/07.
Is there a way I can change the criteria prompt so that it read both dates entered as inclusive in the query. I am currently using Between [enter earliest date:] and [enter latest date:]
good morning all, I am using the afterupdate event on this forms field. If this date field is NOT EMPTY, then i want the next field to be visible. Here is the code i have
If Me.quotedate = ????????? Then Me.quotehow.Visible = True Else Me.quotehow.Visible = False End If
I have done this with text fields where i have a defined data to be = to, but for a date field what do i put in ?????????? to show is not null?
I have a query that returns hours of vacation time taken for each specfic day of the year. But if no time is taken on for example Jan. 19th, that date do not show up in the query. I am wondering is it possible to return all 365 days and display a zero,if nothing is booked for that day. Attached is the db.
So I went searching through the forums and found a thread that provided coding for getting the # of years and months from two dates:
Function fAgeYM(StartDate As Date, EndDate As Date) As String 'Purpose: Returns the difference between StartDate and Date in full years and months 'To call: ' ? fAgeYM(#1/21/04#, #1/19/06#) 'Returns: ' 1 years 11 months
fAgeYM = Int(intHold / 12) & " years " & intHold Mod 12 & " months "
End Function ------------------------------------------
That's perfect for what I'm wanting but I'm doing it with Hire/Term dates. So I want to be able to see how long past and present employees have worked in the company. Obviously that makes the Term field Null at times. With the coding above it requires a date to be in the EndDate field. How can I change it to allow for a null field in which the field would essentially be the current day's date? [Now()]
A form with a text box expecting a date has the validation property as, >#1/1/2010# And Is Not Null. If a date is entered older that this the error is caught however if the field is left null it is not caught as an error. I have tried several modifications of this statement with the same results.
If (DataErr = 2113) Then Select Case Screen.ActiveControl.Name Case "DA_Start" MsgBox "invalid date", vbExclamation Me.DA_Start. = Null End Select Response = acDataErrContinue End If End Sub
This will pop up the error message, but will trigger the debuger when gettin to "Me.DA_Start = Null"
I'm really lost on this one... How do I find the # of days it takes to complete a task between two date fields. For example: [DateRec] field is the field the app was first input to the database. [DateNumb] field is the date the first task was completed. So I understand, =DateDiff("dd", [DateRec], [DateNumb])) But I'm getting an #Error because often these two dates are the same date/equal, Or/ the DateNumb field may pre-date the DateRec date. Or/ one or both fields might be Null (blank). How do I set the formula on this?
Bottomline,I need to find the average # of days it takes to complete a task between two date fields for all tracking records. How do I do That???? HELP!
I have form that user can filter the records and generate a report but I have difficult trying filter null date.
If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...
I'm trying to search through a table with serial card IDs, Order Numbers, and Ship Dates. I'm search through the Serial Card ID column and making my code work so that if the serial card ID exists and its ship date is between 9/30/2001 and 10/1/2011, then retrieve the associated order number and put in array. All of this works perfectly except for one tiny part : it's not returning a null for either unfound serial Card ID or serial card Ids that exist but their ship dates do not qualify. I would like to return a null value for these fields into the array as well as keep the found values in the array. The whole point of all of this is so I could count the number of rows for all the found order numbers and return a total value with that number.
I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
I am new to Access. I am after a vba script to run an update query.
I have a table named MYOB_Invent. I have a Query Named MYOB_Update.
The query is showing records where my field named Test1 is null.
I want to update records with null in the field to today's date (the date of the day that it is run) formatted as 18/08/2014 and the field next to it is named Updated (which is yes/no) from un-ticked to ticked for the updated values.
I would like a vba script to automate the process as I want to automate it via a commandbutton for users.
