This seems easy, but I can't get it right. I need to find the DATE of the THIRD Wednesday each month. (Actually, it is for a query criteria that will return TRUE if Now() is a "third Wednesday.") The problem is that depending on the month starting date, number of days in month prior etc. this date can be either in the 3rd or 4th week. Also, you can't just add 28 days to the prior month because it can vary.
View RepliesI need to create a function that will return the last day of a month, regardless of which month it is and, in the case of February, which year it is. (Gilbert & Sullivan got it right - the powers that be played a cruel joke on us with leap years!) I could hard-code in the months into my code, but there's got to be a way to do it without typing in everything manually...right?
View 1 Replies View RelatedHere is my current code:
FROM tbl_COMPANY INNER JOIN tbl_TESTS ON tbl_COMPANY.CompName = tbl_TESTS.CompName
WHERE (((tbl_TESTS.DateBilled)=#3/5/2008 7:15:02 AM#))
ORDER BY tbl_TESTS.CompName;
I no longer want to refrence the exact date and time. How can I change this to look at the most recent Wednesday? So if today is Friday it will look back 2 days, if today is Monday it will look back 5 days, if today is Wed it will look at today. I need it to find the most recent Wed.
We have an update Query that bills updates a Date field every Wed. I then update this query to pull a few reports. I think there is a better way to do this right? Thanks.
dear all
how to calculate a date which is falls on every wednesday using ms access
for eg
this week enter date : 05/03/2007 and then save
next week automatically display date : 14/03/2007
later next time, enter date : 16/03/2007
next week automatically display date : 21/03/2007
i don't think this is the right formula
=DateAdd("d",7,[run_date])
if i keyin run date : 03/03/2007
it will display using above formula : 10/03/2007
but i want display run date : 07/03/2007 ( falls on wednesday )
what if keyin run date : 05/03/2007
it should be displayed : 07/03/2007 ( falls on wednesday )
any other way the right formula
thanks in advance
I have a report with a date field and want to sort it by week starting on Wednesday. I currently have the week starting on Sunday.
View 3 Replies View RelatedI am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
I have a table full of dates of meetings through out the year...
Example
tMeetingDates
16th August 2005
18th September 2005
19th October 2005
23rd November
-----
i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.
I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.
i then want this date to be displayed in the Report.
So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...
Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...
we only ever have one meeting a month!!!!
Please help
Andy
Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?
View 6 Replies View RelatedHiI have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.kind regards in advance.peter
View 5 Replies View Relatedplease help me
i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..
any ideas please
Hi all:)
I am looking for some advise, I work for a small service company and we our trying to computerize our service jobs through the use of access 2007, We have completed 80% of the database but are having a few problems completing, for example the search function on some forms etc, I have searched the web for help and have posted the problem on this forum a number of times but I am still unable to get it to work myself as I am new to access , the problem is I need the database completed asap before we take on a bigger contract and I am running out of time, so I was wondering if anyone knew of anyone / or any company out there that could have a look at the search form, I would obviously pay for it, I have tried to approach a few companies but they will not take on such a small job, they only want to take on the development of the entire database?
Anyone know of anyone?
Jackie :)
I'm searching through some text fields like the following:
Ann12 #1
Ann12 #2
Ann12 #3
Ann1 #12
Ann2 #14
ect...
I'm doing a like query
Like "*#*2*"
However, the # is the keyword for Date, so what do I use instead?
Hi
I hope someone can help with this as I can't seem to get this query to work.
I am trying to use a query to search for the highest value in a column but based upon a specific criteria.
i.e. in the column will be ABC1234, ABC1235, ABC1236 and BBC1234 and I want to get the highes 'ABC' value
I looked on the forum and found this code
Dim strSQL As String
Dim MyValue As DataType 'Replace the proper datatype of field
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tableName.ColumnName FROM tableName ORDER BY tableName.ColumnName DESC"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyValue = rs!ColumnName
rs.Close
Set rs = Nothing
I've modified the query to this
strSQL = "SELECT tableName.ColumnName FROM tableName WHERE tableName.ColumnName Like 'ABC*' ORDER BY tableName.ColumnName DESC"
This doesn't bring anything back but I was expecting 'ABC1236'
I'm new to using 'Like' so this could be the problem.
Your help is much appreciated.
Greetings all,
It's been a while since I've had to turn to you for help. I'm usually able to find what I need in searching through previous posts, but no luck this time around.
I am trying to write a query criteria that will return
BAC-5, BAC-8 And BAC-12
I can get the -5 and -8 with:
Like "BAC-[58]"
But I can't figure out how to get the -12...
The actual part numbers are quite a bit longer and there are hundreds where I only need specific dash numbers returned.
I'm sure this one will be simple, I just couldn't seem to figure out how to search for it.
Thanks in advance!
Hi guys
I have a DB for insurance permits.
tblSubtractors
ID
NAME
ADDRESS
PHONE
tblPolicy
ID
SubbyID
POLICYID
Insurer
PolicyNumber
ExpiryDate
tblPolicyTypes
ID
Name
Most subcontractors will have a policy number for each type of policy in tblPolicy. But is there a way I can run a query to show each subby that doesnt have one of the policies? As an example, the query would show me that ABCcontractors doesnt have Marine Transit insurance. XYZContractors doesnt have Public Liability, etc
I know how to show all the policies that have expired, but I'm looking to identify which ones dont have a policy at all
:confused:
Hello,
I have this code that I use to update a record:
Code:<%Dim idid = session("loginID")Dim rsSaveSet rsSave = Server.CreateObject("ADODB.RecordSet")rsSave.open "log", conn, adOpenDynamic, adLockOptimistic, adCmdTablersSave.find "id=" & idDim Datumlogout, Uurlogout, loggedoutOKrsSave("Datumlogout") = datersSave("Uurlogout") = timersSave ("LoggedoutOK") = ("true")rsSave.updatersSave.closeset rsSave=Nothing%>
Can someone help me please, I would like to have the code changed to:
When the ID not excists, that there is a new record written.
Thanks in advance!
Hello,
I've been looking through the archives and learnt a fair amount about date manipulation, but am still a bit stuck. I'm sure there is a way to do this and would appreciate any ideas:
Basically I am monitoring changes in Fuel level over months. For each month, I have a variety of readings, each on a different table, including sales from that tank, delivery in to it, and most importantly a closing dip reading taken at the end of the month.
In order to figure out the change in Fuel level over each month, you need an "opening" figure : this is taken to be the closing dip value of the previous month. And this is where I'm getting stuck!
I am writing a query to generate new table which combines all the relevent values. I have managed to link them through the date field, even though the specific date is not identical what matters is that it is the same month. Effectively I am trying to add a new 'column' showing the opening value, where opening value = previous months closing value for this tank.
I noticed there is a post talking about previous month data <a href="http://www.access-programmers.co.uk/forums/showthread.php?t=39708&highlight=previous+month">here</a>, which gives me some ideas but I'm not sure how to go about putting this into the SQL query. I can't use Date() because I'm interested in more than just previous to THIS month - the data spans back over a year!
I'm still learning Access so this might be a simple question - if so please point me to the relevent parts of the help file!
many thanks
Catherine
I have a customer concerns database that contains the dates for when the concerns were reported and tyhe dates for when the concerns were resolved. I am trying to make a query that finds the average of how long it takes for the concerns to be resolved. How can I do this?
View 1 Replies View RelatedNow this is not like me, posing a question, but I thought its about time I codes the app correctly.
My app opens up any given spreadsheet. The first task is to establish the last row used in the spreadsheet. Normally you would loop until there is nothing in a cell in column A, whatever.
However the way the spreadsheets are provided there may be pleanty of blank rows. I have tried using the Edit GoTo Special LastCell and converting it to a macro
But when I run this VB it does not like it for some reason.
I am sure someone out there has come up with a solution by now.
CodeMaster::cool:
I have a table, with 4 records in it. 2 records have the "date" field for 01/11/2005, and 2 dates are for 02/11/2005.
Is there a way to just query the "current" date? Weather it be in a pop-up box once the query is selected, asking for the date? or is there a restricted word within access2003 that lets you find the current date?
I would essentially like a command button within a form to allow the user hit teh button, and it will automatically find those records within the DB with the current date(set to system clock) and export it to excel(i have this portion, the exporting to excel complete)
Any help would be greatly appreciated.
Connor
ok ihopefully i wont make this too complicated to under stand.
i ahev a form which will populate a form from a query. the query is pulling info out a table included int hti sinfo is the date when it was entered. in the criterai of the query i hve added this code
Between [Forms]![Boxes Reviewed]![txtFromDate1] And [Forms]![Boxes Reviewed]![txtToDate1]
whcih means that the date must be between the two differnt dates that must be entered on the form.
the problem is, is that a value can be entered more that once with different dates. i woul dwant the report just to shouw the most recent date. i considered doing a query which requested all max dates from the table and then having a query calling forma query. but i do not know enough on how to get just the most recent date.
so if i wrote a query which held
box num, shelf num, resp_person, and date. what would i have to put int he criteria to only bring back the entery witht he most recent date.
sorry if i have rambled and look forward to your help
lots of customers. lots of orders. i need to query for the first order of each customer only. thoughts?
View 1 Replies View RelatedHi
I apologise if this is really basic - I am learning as i go along. I have designed a database. Most of the info is stored in the clientinfotbl. On the main form, I would have provided an option for users to search for a record based on its ID number and then pull up various forms accordingly. I am using the "find and replace" option to do this. My first question is, is there a better way? This feels a bit clunky!
Second question is, once I have entered a new record in the "newclientfrm" and go back to the mainfrm and search for that record, it cannot be found (once I have closed db and gone back in, it can then find it). Is there a way round this?
Any help would be hugely appreciated :)
I would like to make a value equal to another value in another form, but it keeps telling me that it can't find the Form.
newID = Forms![frmLogIn]![UserID]
Error message is 'Access can't find the form 'frmLogIn' referred to in a macro expression or Visual Basic code'.
I have rechecked the spelling and caps on the form name 5 times so that's not it :(
James
Here's a real stumper... I have a form with a subform based on the account number in the main form. This form has been running without a hitch for maybe two years! Now, while updating an unrelated form in the same database, I test the new additions. I get an error message that the main form cannot find the subform, and it goes on to display a blank white box instead! Here's what I have already tried:
1. Checked the name of the subform. It's correct, just like it's been for 2 years.
2. Running compact/repair in case of corruption
3. Deleting, then re-placing the subform on the Main Form.
One other wierdism - after getting the white box where the subform belongs, and after being told that the [subform name] can't be found, I can right-click on the white box, select the "Form" option, and the subform appears! To me, that means that the "can't find" error message is a misnomer, but I'm at a loss as to where to look from here.
I guess what I'm asking is if anyone has seen this behavior of a form "not finding" its subform, yet it can be forced to display it with a popup menu? Also, any experiences with this or any related information would be appreciated.
Thanks-
Scott