I need displaying the max date sql code below;
PHP Code:
SELECT TblDietPlan.DietPlanID, TblDietPlan.ClientID, TblDietPlan.MealDate, TblDietPlan.MorningSnack,
TblDietPlan.AfternoonSnack, TblDietPlan.EveningSnack
FROM TblDietPlan
WHERE (((TblDietPlan.ClientID) Is Not Null) AND ((TblDietPlan.MealDate)=Max()))
ORDER BY TblDietPlan.MealDate DESC;
I need to display the results of a query on screen when somebody clicks a button on the form. So I put code in the event of the click that looks like this:
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
Dim stDocName As String
stDocName = "HHC Report"
DoCmd.OpenQuery stDocName, acPreview
Exit_Command20_Click:
Exit Sub
Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click
End Sub
So far, so good, but my problem is that this query is one row with a lot of columns - that creates a lot of pages to look through. Is there a command that would loop it around on the same page, or rotate it clockwise so it's going down the page instead of across?
Hi,
I am creating an access DB that has job listings
and displays the DB online.
I am creating tables rows for 'BeginListing' and 'Endlisitng'
etc that are stored as a Date
How do I get the asp/vbscript to check the date 'EndListing'
of the entry and to stop displaying it when the date is
later than today (so it takes them offline when the date is
expired)?
My script is like this and loops through each record set displaying the
contents:
'Loop through the recordset
Do While not rsJobPostings.EOF
'Write the HTML to display the current record in the recordset
Response.Write ("<div class='sitetext'><br> Job Number: ")
Response.Write (rsJobPostings("Job_Number"))
Response.Write ("<br>Job Heading: <b>")
Response.Write (rsJobPostings("Job_Heading"))
Response.Write ("<br>")
Response.Write (rsJobPostings("ListingEnds")) .... you get the idea.
Cheers thanks.
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
Dear All:
How do I go about displaying the current month and year in a report? I reckon this would be in a textbox.
Any help is greatly appreciated.
Regards,
Dion
I have a textbox on my form that is for "week of", always a Monday. There is vba to populate the appropriate Monday when the user selects something other than Monday via the date picker. This code is in the on change event. However, this is not friendly to a user entering a date in this field. I did try after update event, but that requires user to leave the "week of" field. This is not acceptable in this instance, there are other form fields that change as a result of this "week of" value.I also tried evaluating the length of the text or else exiting the code. However, I was expecting the date being returned from the date picker to always be 10 positions, but for March 3rd, it would return 3/3/2015...which is only 8 positions.
View 2 Replies View RelatedIs there a simple formula to return the fiscal year quarter that applies to a date? I simply want January thru March to show as Qtr 1, etc.
thanks
I have six textboxes in my form. First 5 textboxes have dates ( MMDDYYYYY). In the sixth textbox I need to display the minimum date among these five dates.
View 10 Replies View RelatedI have a series of forms in my shiny new database and I have added the auto time and auto date fields to the form headers. I have added the code to keep the time ticking over and it all works fine.
Unless the is no data. If for any reason there is nothing in the Details part of the Form, the date and time do not display. As soon as any data shows up, so do the date and time.
The VBA code is running in either case so I am a little baffled. Usually Google is my friend but not in this case.
I am using Access 2013 on a win 7 machine.
I have Query which has dates in multiple columns. I need to know if there is a formula that will allow me to display the oldest date and the newest date?
Below is the output of my Query in Excel. I need the output to have two additional columns: Beginning Date and End Date. Based on the example below, I would need the beginning date to show 6/21 and the end date column to show 6/23 so when I export to excel, it shows these two dates. In Excel, there is a min/max formula that can do this but it looks like the min/max in Access only displays the min/max data? Unfortunately, creating this formula is not an option after the fact. Secondly, I need the word "DIRECTION" to show in it's own separate column in the in the query. I don't know if I can do this without adding another table to the Query or if I need can just add a column in design view and put something in the criteria that will do this.
I have included an example of my current output and what I need the output to read.
Current Output
Sunday
In
Out
Monday
In
Out
Tuesday
In
Out
[code]....
I have recently started learning to use Access and all is going very well except for the date. As can be seen from the images below I have set up my date function correctly in Control Panel and everywhere I use a date it is displayed correctly. In Access however numbers are added which I cannot figure where they are coming from and don't want them there. In the second image you can see what the various Format options look like. When long format is used in a Form or Report it displays those additional figure. I cannot get 2015 to display but get 15316 or similar instead.
View 2 Replies View RelatedHi All,
I have a field named "PICK_DATE" in text format where pickup date is stored in DD/MM/YYYY format.
Now I wanted to generate a report with the order by clause of PICK_DATE. But it is not giving me the desired result. I want to see the latest PICK_DATE at the top of the report in desending order.
Can any one pls help me out to solve this problem ? :confused:
Thanks in advance,
Jignesh
Hi,
I was wondering how difficult it would be to do the following:
1. A user opens up a form.
2. The user uses a combo box to select his/her name.
3. There is a date combo box on the form and by default, it is set to today's date. So when the user selects his/her name, the records for today are displayed. Changing the date will show only the records for the date shown.
4. The user will have the ability to add and delete records specific to the criteria chosen in the text boxes. Adding new records will only add records for the date chosen in the combo box. E.g. if he/she adds a record for today, it will only be seen when today's date is selected.
Will a subform have to be used for something like this? Or could it be done with one form using filters?
Any thoughts and/or approaches on this would be greatly appreciated.
TIA.
I'm trying to include some text and the contents of a date field but having trouble with the syntax.
It wont display the contents of the date variable
I have
"Effective Date = " & #" & Me.DebtPrincipalDateFld & "#
I've tried a number of combinations and searched all over but to no avail
Hi,
Need help on how to display queries by using MsgBox. Reason being is would like to view my selection criteria to check why is it not displaying records though they exist in table.
Many thanks.
If you have a one - many relationship between data, normally in the table it is captured, you would find multiple rows for every 'thing', each row detailing different 'functions' performed on it.
Is there a way that one can display these functions horizontally, instead of vertically, i.e, have one row per 'thing', instead of multiple rows.
I have created a query to limit the list in a combo box to display the months and years of the date field in a table. Below is the code that I am using. This works fine except this displays the years within the months - for example:
January 2012
January 2013
January 2014
February 2012
March 2012
March 2014
April 2012
May 2012
May 2014
I would much prefer it to sort by month and year:
January 2012
February 2012
March 2012
April 2012
May 2012
January 2013
January 2014
March 2014
May 2014
Code:
SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName
FROM MainTBL
ORDER BY Month([QTDate]);
How to change this code to make it sort on Month and Year, not just month?
I have a combo box which queries a table and then stores said tables primary key field in a field for my main table. The table queried has two fields; one for the primary key and the other for the string, such as "chris".
My problem is when I run new query from the main table it shows the bound column, which is the primary key, instead of the string column which would display my name. I can't figure out if there is code that I should deposit in the criteria field that would tell the query to display the name field (ala "Chris") instead of the bound column field (ala primary key).
Do I need to add something to the criteria field or run an expression that says display column 1 instead of column 0 in your quey?
I want to run a query to show all my outstanding orders between two dates. I've done all this correctly and it works fine. However I only want the query to show me the records where the checkbox on 'tblOrders' is unchecked. For obvious reasons, I don't need to see these records, just that records that have been checked, therefore are outstanding.
View 5 Replies View RelatedHello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have a query that carries all the properties in our database, and data for when our company 'worked on' properties. Each property falls within a certain province and municipality (this is slightly irrelevant info) , we worked on properties in a period from 2009 - 2013/06/30 this period is called '2009', and we have again worked on properties during a period 2013/07/01 onward - this period is called '2013'.
Now I require this query to display all the properties, but only the entries in certain fields, that relate to '2013'.
I have about 7 fields where this date (either 2009 or 2013) can be displayed, I tried to filter them all simultaneously by using the criteria: Is Null Or "2013", in every one of these fields, but this criteria results in me losing entire records that contained 2009 data, instead of the data simply not being displayed. Even in cases where some of the 'date' fields contained 2013.
How I can keep all records but simply display info relating to work we did in the 2013 period?
1 I have a delivery method column and I want to only show 1 type for each method. How would I do that? Is it in expression builder?
View 1 Replies View RelatedI'm trying to make a search form that makes use of queries that search a table based on what fields I have. How would I go about having the query display all the fields for the record(s) that match the search criteria? This is for Access 2010.
As far as the query is set up, I have several fields with a search criteria, and the others are for displaying the relevant information about the results (since they're not search criteria, I used "Like '*'"). When I ran the query, it doesn't come up with anything - even if the record actually exists in the table with specified criteria.
Have a query which looks up an address using the text input into a textbox by the user.
What I'm now struggling with is getting the query result into the textbox.
Am still new to access and am hoping this is something fairly simple.
I have a simple database with a query that "SUM"s an amount.
I created a form with a sub form that is linked by Account number. I want to be able to open the form in datasheet format and it to display the SUM value from the query.
When I open the form it responds with #Type until I click the + symbol to open the linked query then it updates the #type field with the data from the query.
I work at a program where we have individuals try out for a field (coming from various backgrounds). The program has been in place since 2012, and I wanted to start looking at the data for analysis and comparing trends, etc; I imported our master file (excel) into Access. It's in one big table, column headings include roster number, name, gender, type, FY (fiscal year), and finalcode (Select, Non-Select - but put it in as 1 or 2 in the field)).
I created a query, and was able to get data I was looking for.I then set it to Pivot Chart (bar graph).
Bottom (Believe its X) Axis: I have Type, Year.
Right Side of Chart (TypeCodeNumber)
Top of Chart: Count of CodeNumber
It gives me the totals (whether select or non-select) just fine, but I have been racking my brains, watched tons of YouTube videos and read a lot on how to make it give me a percent. I found a way to go Percent on the left side of the bar chart, but the Values go up to say 3300%. I know the problem is with the Count up top.