sql = sql & " WHERE (((tblAccountBalance.DateLastPayment) < #" & reminderdate & "#))"
Im trying to select all the entrys in a table where the date of the last payment is before a variable 'reminderdate' this works fine if both dates are in the same month ie, DateLastPayment = 26/11/05 and reminder date = 30/11/05, but if the DatelastPayment is 26/11/05 and the reminder date is 01/12/05 then nothing is found.
What am I doing wrong?
Hello all!
How can I create a query that the select depends on a variable that the user introduces?
would it be something like:
Select [@var] from table ????
it is not working. Example:
@var would be number from 1 to 31 (according to a day on the month that the user wants to view), then the result for it, is the number that the user introduced, instead of the vaule kept in that number...
Hope i make myself clear....
Thanks in advance!
I have two tables naming
1.) Criteria and 2.) IndicatorData.
Criteria table has (CriteriaID, CriteriaName,CriteriaValues) Fields. Example( CriteriaName-'Gender 'and values would be 'Male' and 'Female').
IndicatorData table has fields such as (IndicatorDataID, IndicName, Criteria_Code1,Criteria_Value1,Criteria_Code2,Crit eria_Value2,Criteria_Code3,Criteria_Value3).
I made the relationship between the IndicatorData and the Criteria as 1 to many where Criteria_Code1,Criteria_Code2 and Criteria_Code3 ...... up to Criteria_Code5 fields of IndicatorData table reference to the CriteriaID of Criteria table.
I made the sql qury to get all Criteria Names belong to a particular IndicatorDataID but it didnt work. my query is
"SELECT Criteria.tCriteriaName
FROM Criteria INNER JOIN IndicatorData ON (Criteria.nCriteriaId = IndicatorData.CriteriaCode5) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode4) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode3) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode2) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode1)
WHERE (((IndicatorData.nIndicatorId)=9));"
IS IT POSSIBLE TO GIVE THE RELATIONSHIP BETWEEN INDICATORDATA TABLE AND CRITERIA TABLE LIKE THAT OR DOES MY SQL QUERY HAVE SOMETHING TO DO WITH IT...?
answer would be greatly appriciated.
Thanx + Regards
Kosala
I have two tables naming
1.) Criteria and 2.) IndicatorData.
Criteria table has (CriteriaID, CriteriaName,CriteriaValues) Fields.
Example( CriteriaName-'Gender 'and values would be 'Male' and 'Female' and CriteriaName-'AgeGroup1' and Values-'<30','30-40','40-50','+50').
IndicatorData table has fields such as (IndicatorDataID, IndicName, Criteria_Code1,Criteria_Value1,Criteria_Code2,Crit eria_Value2,Criteria_Code3,Criteria_Value3, IndicValue).
Example-(IndicatorDataID-'ind001', IndicName='No. of participants', Criteria_Code1-'Gender',Criteria_Value1-'Male' and IndicValue-200.)
I made the relationship between the IndicatorData and the Criteria as 1 to many where Criteria_Code1,Criteria_Code2 and Criteria_Code3 ...... up to Criteria_Code5 fields of IndicatorData table reference to the CriteriaID of Criteria table.
I made the sql qury to get all Criteria Names belong to a particular IndicatorDataID but it didnt work. my query is
"SELECT Criteria.tCriteriaName
FROM Criteria INNER JOIN IndicatorData ON (Criteria.nCriteriaId = IndicatorData.CriteriaCode5) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode4) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode3) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode2) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode1)
WHERE (((IndicatorData.nIndicatorId)=9));"
IS IT POSSIBLE TO GIVE THE RELATIONSHIP BETWEEN INDICATORDATA TABLE AND CRITERIA TABLE LIKE THAT OR DOES MY SQL QUERY HAVE SOMETHING TO DO WITH IT...?
answer would be greatly appriciated.
Thanx + Regards
Kosala
I'm very new to Access 2000. I'm working with 3 tables.
I finally got this sql statment to work:
SELECT [tlkp.Language].[LangID],
[tblRawData].[LangName],
[tblApplication].[AppID]
FROM (tlkpLanguage INNER JOIN tblRawData
ON [tlkpLanguage].[LangName]=[tblRawData].[LangName])
INNER JOIN tblApplication ON [tblRawData].[AppID]=[tblApplication].[AppId]
How can I change it to UPDATE?
I want to update the tblApplication.LangID = tlkpLanguage.LangID using the joins described in the select statement?
There is no LangName field in the tblApplication.
I have tried everything and I keep getting syntax errors.
I have a select clause, it executes fine, BUT the totamt is not calculated. The field is Amount. The code is not working.
strSQL = "SELECT Account, Dept, " & "Sum([Amount])" & " As [totamt] FROM TB201410 GROUP BY Account, Dept;"
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
I have a query in which I have a long list of orders and their entry date and due dates. I would like to calculate the number of working days between the both in order to see if the supplier keeps his theoretical delivery term.
Can some one give me a way to achieve this in my query:
DUE DATE.........ORDER DATE.........DELIVERY
03-03-2007 -/- 24-10-2006 = working days
many thanks in advance
There are a variety of date functions but I specifically want to show the date or the first working day after the 25th of each month.
Eg:
if 25 Oct is a Monday then result = 25 Oct
if 25 Oct is a Sunday then result = 26 Oct
Is it possible to select 1 MAX date from multiple date fields for a record?
For example, on an employee record there are 10 date fields, each for a performance review date. Some employees may have date fields 1-3 with values, some with just 1, others 1-5, etc.....depending on how many performance reviews they've had.
Is there a way to pull the MAX review date for an employee, knowing that the MAX date could reside in Field 1 for a certain employee, and could be from Field 7 for another?
I appreciate the help guys!
SELECT count(Workitem) AS ["Total Workitem"]
FROM qryReport
WHERE qryreport.date >(08/10/2005); 'MM/DD/YYYY
Secondy how do i supply dates dynamically.
I am using this as a criteria for a Query that is pulling the 2 dates from a form - Report Date Range.
The reason I am trying to do this is I have a cumulative report that needs to run for the fiscal year (1/1/xx to 12/31/xx). It will always use 1/1/xx as the base date for the range. I am trying to pick up the year because it will change depending on the periods/years picked.
Between #1/1/ & Year ([Forms]![frmReportDateRange]![txtBeginningDate]) & # And [Forms]![frmReportDateRange]![txtEndDate]
When I run the query I get "The expression you entered has an invalid date value."
I am trying to concatenate the 1/1/ with the year and obviously that is not working. Any ideas?
Thanks in advance!
Hi there,
I'm tearing my hair out over a simple issue, hoping someone can help: I have a JetSQL stmt where the BETWEEN...AND... just won't work.
I have registration records between 8/08/2005 and 11/08/2005, and I want to be able to do date ranges. BUT when I use "SELECT * FROM registrations WHERE createdate BETWEEN #9/08/2005# AND #11/08/2005#" I get no results.
BUT when I use "SELECT * FROM registrations WHERE createdate BETWEEN #8/08/2005# AND #10/08/2005#", it spits out all the registrations, even ones with a createdate of 11/08/2005!
What gives? What am I missing? Any wisdom, HUGELY appreciated!
Hello
I have a query where i want to find out all deliveries which are older than 30 days but i want to exclude all weekends.
I know theres a networkingdays function in excel where you can do this but i want to do this in access. Please could any one advise how to do this?
Hello,
I am having a problem w/ my UPDATE statement when the date value is included in the statement. I have tried surrounding the date variable (strDate) in "#" and w/ and w/out single quotes to no avail. I doesn't matter if I dimension strDate as a string or a date. I know there must be a simple solution. In short, the UPDATE statement looks like this:
strSQL = "UPDATE tblTasks SET POC = '" & strPOC & "', Date = #" & strDate & "#, Task = '" & strTask & "' WHERE id = " & lID & ";"
If I remove Date = #" & strDate & "#, then the query works.
These also fail:
Date = #'" & strDate & "'#,
Date = '#" & strDate & "#',
Date = '" & strDate & "',
Any help would be greatly appreciated. Thanks.
Ken
Hello,
I am having a problem w/ my UPDATE statement when the date value is included in the statement. I have tried surrounding the date variable (strDate) in "#" and w/ and w/out single quotes to no avail. I doesn't matter if I dimension strDate as a string or a date. I know there must be a simple solution. In short, the UPDATE statement looks like this:
strSQL = "UPDATE tblTasks SET POC = '" & strPOC & "', Date = #" & strDate & "#, Task = '" & strTask & "' WHERE id = " & lID & ";"
If I remove Date = #" & strDate & "#, then the query works.
These also fail:
Date = #'" & strDate & "'#,
Date = '#" & strDate & "#',
Date = '" & strDate & "',
Any help would be greatly appreciated. Thanks.
Ken
I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.
The query is supposed to pull all data where the "Date Overdue" field is less than today.
"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.
It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.
"Date Overdue" is set to this value:
Code:
DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
"Date Overdue" has the criteria "<DateValue(CDate(Now()))"
I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..
The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.
I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:
"Syntax error (missing operator) in query expression 'DATE OVERDUE' "
NOTE: I'd like to add that this is just a regular Select query.
Code:
SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))
AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS],
[Code] .....
I am trying to get a text string from a table to convert to the proper date format in access 2003.
The text string is formatted as 140930 for Sep 30th, 2014 for example.
I have tried using the formula following formula: Format([PCCSDTA_DCSDIU]![DTEAVL],"mm/dd/yyyy") but this format returns the date 11/06/2285 instead of the desired 09/30/2014.
I am still relatively new to Access.
Today is 13 May and this code is supposed to return the date of next Monday (19 May)
IIf(Weekday(Date())=1,Date()+1,Date()-(Weekday(Date())+9))
but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)
My begin week is Sunday (1) ,
date formula that I was using in access 2007 doesn't seem to be working in 2010.
The formula that I had was
Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1)
And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3 ,31).
The calculation allowed me to count holiday hours taken between 1st April and 31st March. The problem is that it was working up till 31st march but is not now showing holidays taken since 1st April.
I have a subform that is in Datasheet view. If I open this subform on it's own, the Date Picker works for the Date field. However, if I open the main form with the subform on it, the date picker doesn't work? Nothing happens when I select a date on the calender.
I am using MS Access 2010.
I am trying to calculate the working days Based on all web searches I am unable to find the specific scenario I am working in Auto industry, which means auto industry usually close twice a year for any reasons, let say in July for one or sometime two weeks and in December depends upon the Christmas date usually from December 20 till Jan 01
Now my question is when i am enter the holiday details in table do I have to enter line by line date e.g. july 01, 02, 03 (I am able to understand, how this works but still not yet tried) OR july 01 to July 07 (which make sense, but unable to find how to use date range to calculate working days)...
Why is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
I have set up a query that includes:
An assignment start date
An assignment end date
and in addition a column has been set up to show me a Start Date where Assignment End Dates are Null i.e all live assignments.
When I enter a date only results for that particular date are visible.
Can anyone suggest how i can expand this to include all live assignments?
Thanks
Hello,
I'm trying to make a report query work but with no succes so far. Can anyone please help me?
I've got a multiselect listbox and a txtbox (txtCursisten) where the results from the multiselect listbox appear.
I want to get the results out of a table (tblAbsentie) where I can set a start and end date AND use the people selected in the txtBox. So far I've got a NOT working query, a report based on the query and a button on my form.
The VB code behind the "show report" button is this:
Private Sub cmdAbsentiePerCursistPerPeriode_Click()
On Error GoTo Err_cmdAbsentiePerCursistPerPeriode_Click
Dim db As Database
Dim Q As QueryDef
Dim sql As String
If IsNull("Me.txtCursisten") Or Me.txtCursisten = "" Then
Exit Sub
Else
Set db = currentdb()
Set Q = db.QueryDefs("qryAbsentiePerCursistPerPeriode")
Q.sql = "SELECT tblCursist.Naam, tblAbsentie.Datum, tblAbsentie.Lesuur, tblAbsentie.AantalLesuren, tblAbsentie.Deelkwalificatie, tblAbsentie.Docent, tblAbsentie.Gemotiveerd, tblAbsentie.Reden, tblAbsentie.Status, qryCountLesuren.SumOfAantalLesuren" & _
"FROM (tblCursist INNER JOIN qryCountLesuren ON tblCursist.OVnr=qryCountLesuren.OVnr) INNER JOIN tblAbsentie ON tblCursist.OVnr=tblAbsentie.OVnr" & _
"WHERE (((tblAbsentie.Datum) Between [Voer begindatum in] And [Voer einddatum in]) AND ((tblAbsentie.OVnr) In ("Me!txtCursisten"))); "
Q.Close
DoCmd.OpenReport "rptAbsentiePerCursistPerPeriode", acPreview
End If
On Error GoTo 0
Exit Sub
Exit_cmdAbsentiePerCursistPerPeriode_Click:
Exit Sub
Err_cmdAbsentiePerCursistPerPeriode_Click:
MsgBox "Selecteer klas en cursist(en)."
Resume Exit_cmdAbsentiePerCursistPerPeriode_Click
End Sub
I get a syntax error when pressing the button. Please help...
SeBasTiaan
I have a table that shows me 3 fields:
PROCESS
EFFECTIVE_
OPERATION_
My issue is each process and operation may be there multiple times due to multiple Effective dates. I only need to see each Process and Operation one time based on the latest Effective date. Below is what I have NOW:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-01 10
1/010/1-8TCOWLFEEDER 2005-11-01 20
1/010/1-8TCOWLFEEDER 2005-11-01 30
1/010/1-8TCOWLFEEDER 2005-11-01 40
1/010/1-8TCOWLFEEDER 2005-11-03 10
1/010/1-8TCOWLFEEDER 2005-11-03 20
1/010/1-8TCOWLFEEDER 2005-11-03 30
1/010/1-8TCOWLFEEDER 2005-11-03 40
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
This is what I need:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
Out of the records above I would only want to see the records with the 2005-11-09 date. The dates can vary based on processes so I really need something that selects the the latest date for each Process and Operation. I would like to do this in a query or multiple queries.
Thanks for any help.