Format Datepart func to return specific rows based on datestored via qry to excel

[tasmontique]

I have an access table that outputs to excel using a query . However what I am trying to do is under the arrival date column specify a criteria based on the Datepart function that only displays output based on specific day of week.

I want query to check the stored date in the arrival column and based on that date that is examined by the date part function only return rows based on day of week .Here is my sql.



SELECT tblflight1.Airl ineCode, tblflight1.Flig htnuminout, tblflight1.STA, tblflight1.Rout eFrom, tblflight1.STD, tblflight1.Rout eTo, tblflight1.Conf iguration, tblflight1.Equi pment, tblflight1.Prog ramIN, tblflight1.Rema rks, tblflight1.Arri valdate, tblflight1.Depa rturedate, tblflight1.Week start, tblflight1.Week end
FROM tblflight1
WHERE (((tblflight1.A rrivaldate)=Dat ePart("w",Date( 2))));

I keep getting an error can someone please point me in the right direction.
What I am trying to do in the where clause is to return dates based on arrivaldates for dayofweek monday

I am sure that I incorrectly formatting DatePart("w",Da te(2))));

[MMcCarthy]

This will return all records where Weekday is the same as the current date.

SELECT tblflight1.AirlineCode, tblflight1.Flightnuminout, tblflight1.STA, tblflight1.RouteFrom, tblflight1.STD, tblflight1.RouteTo, tblflight1.Configuration, tblflight1.Equipment, tblflight1.ProgramIN, tblflight1.Remarks, tblflight1.Arrivaldate, tblflight1.Departuredate, tblflight1.Weekstart, tblflight1.Weekend
FROM tblflight1
WHERE Weekday(tblflight1.Arrivaldate)=Weekday(Date());
 

Mary

[tasmontique]

This will return all records where Weekday is the same as the current date.

SELECT tblflight1.AirlineCode, tblflight1.Flightnuminout, tblflight1.STA, tblflight1.RouteFrom, tblflight1.STD, tblflight1.RouteTo, tblflight1.Configuration, tblflight1.Equipment, tblflight1.ProgramIN, tblflight1.Remarks, tblflight1.Arrivaldate, tblflight1.Departuredate, tblflight1.Weekstart, tblflight1.Weekend
FROM tblflight1
WHERE Weekday(tblflight1.Arrivaldate)=Weekday(Date());
 

Mary

Thank you so much.
I read up on the weekday function but it is still not doing what I intended.Maybe it is not possible in a query that is being exported to excel.
I want to run a query that returns weekday based on the stored value in the table colum eg
SELECT*
FROM tblflight1
WHERE tblflight1.Arri valdate==Weekda yName ;

I tried this but I get an error Datatype mismatch conversion

[MMcCarthy]

Not sure exactly what you mean by this ...

 WHERE tblflight1.Arrivaldate==WeekdayName

Where are you getting WeekdayName from and what is it based on?
I assume Arrivaldate is an actual date.

Mary

[tasmontique]

I was trying to use the WeekdayName function.
However I realize that this WeekdayName function returns a String representation of a day given a number e.g.:

WeekdayName (3) would = Tuesday

My error is I am comparing Arrivaldate(sto red table column date value) with basically nothing WeekdayName (3) .

I want to create a new expression in the query that finds out what Weekday Arrivaldate(sto red table column date value ) is by using the WeekDay function and from there try to build query(criteria) that only returns specific weekday for Arrivaldate(sto red table column date value).

I am clueless about how I would do this in sql.

[MMcCarthy]

I was trying to use the WeekdayName function.
However I realize that this WeekdayName function returns a String representation of a day given a number e.g.:

WeekdayName (3) would = Tuesday

My error is I am comparing Arrivaldate(sto red table column date value) with basically nothing WeekdayName (3) .

I want to create a new expression in the query that finds out what Weekday Arrivaldate(sto red table column date value ) is by using the WeekDay function and from there try to build query(criteria) that only returns specific weekday for Arrivaldate(sto red table column date value).

I am clueless about how I would do this in sql.

How are you deciding which ArrivalDate to base the weekday on for the criteria. Is the query for a report or what?

[tasmontique]

How are you deciding which ArrivalDate to base the weekday on for the criteria. Is the query for a report or what?

The query is for a "report" that is output to excel. The "report" will be based on a select statement that returns information in tblflight1

Which I have done. But presently the query just returns select * from tblflight1 right now into the spreadsheet.
I want to filter or base the query on the criteria of specific arrivaldates.

Initially I thought that if I designed a query that returns information from the table based on Weekday that is Sunday,Monday etc.
Then the arrivaldate column would be sorted based on that criteria.

However, I know think that it might be simpler to just modify an existing form that I have.
This form does a search where the user enters an arrivaldate in the textbox and it returns all the records based on arrivaldate.

I would like to just add a command button eg cmdprint.This cmdprint button would output the results to the preformatted excel spreadsheet based on the arrivaldate.

I know that I have to use vba to do this.
Can someone pretty please point me to a good tutorial to transfer info to a preformatted excel spreadsheet based on results filtered on a form ( by arrivaldate search results.)

Regardless Thanks for all your help thus far

[MMcCarthy]

Try this ...

SELECT tblflight1.AirlineCode, tblflight1.Flightnuminout, tblflight1.STA,
tblflight1.RouteFrom, tblflight1.STD, tblflight1.RouteTo,
tblflight1.Configuration, tblflight1.Equipment, tblflight1.ProgramIN,
tblflight1.Remarks, tblflight1.Arrivaldate, tblflight1.Departuredate,
tblflight1.Weekstart, tblflight1.Weekend,
WeekdayName(tblflight1.Arrivaldate)
FROM tblflight1
ORDER BY Weekday(tblflight1.Arrivaldate);
 

Mary