Organize by date in a Union Query

[douglasrac]

Hi,

I have a union query with date in the format ddmmyyy. This query was created to show birthdays of the month, which works but the order of the birthdays are all wrong, because in order to organize from oldest to newest, access is considering the year, not only day and month. How can I make it to organize by day and month only?

 

[DCrake]

Create a new query based on your union query bring down the birthdate field and format it

Format(BirthDate,"mmdd")

Then sort by this field.

 

[douglasrac]

Create a new query based on your union query bring down the birthdate field and format it

Format(BirthDate,"mmdd")

Then sort by this field.

If I do this it asks for a number when I try to view the query and then instead of date appear codes (what seems to me random numbers)

 

[DCrake]

Have you added a new column such as

bDay:Format(BirthDate,"mmdd")

Sort Ascending

 

[jdraw]

Please show the query.

 

[douglasrac]

Have you added a new column such as

bDay:Format(BirthDate,"mmdd")

Sort Ascending

Ok now it works. But why I need a new column to do this? Seems so simple.

I use format for the phone number and works perfectly. Why for birthday I need a new column, I cant put directly the format?

Well its fine! I just hide the column and be happy forever.

Thank you!

 

[DCrake]

As you want to sort by only the day and the month of the date then you need to create a dynamic column that is only day and month. Then you can sort by this format.

Where does telephone numbers come into this?

 

[douglasrac]

Here is the important part of the code:

SELECT Clients AS Birthday, Birthday Date, Format(Phone,"(00) 0000-0000") as [Phone 1]

As you can see, for the field Phone I manage to put the format directly where I select it. So the query get the phone number and directly input the format I asked for. By this logic, I thought I could do the same to Birthday Date field, but when I do this I get a windows asking for a parameter.

So the solution, as you told me, is to create a new column, pointing to the same birthday field, but this time with the format. Why cant it go directly as phone number field?

 

[DCrake]

That because you are using spaces in your name

Should be

SELECT Clients AS Birthday, Format([Birthday Date],"ddmm") As [Birthday Day]

 

[douglasrac]

That because you are using spaces in your name

Should be

SELECT Clients AS Birthday, Format([Birthday Date],"ddmm") As [Birthday Day]

That was an example. The real code have brackets on names with spaces.

Here is the full code, with your solution. The way I suggested does not work.

SELECT Clientes.[Segurado] AS Aniversariante, Clientes.[Data de Nascimento], Format([Clientes].[Telefone 1],"(00) 0000-0000") as [Telefone 1], Format([Clientes].[Telefone 2],"(00) 0000-0000") as [Telefone 2], Format([Clientes].[Telefone 3],"(00) 0000-0000") as [Telefone 3], Clientes.[Segurado], [B]Format(Clientes.[Data de Nascimento],"mmdd")[/B]
FROM Clientes
WHERE (((DateSerial(Year(Date()),Month(Nz([Data de Nascimento],0)),Day(Nz([Data de Nascimento],0)))) Between Date() And DateAdd("m",1,Date())))

UNION ALL 

SELECT [Dados dos Condutores].Nome, [Dados dos Condutores].[Data de Nascimento], Format([Clientes].[Telefone 1],"(00) 0000-0000") as [Telefone 1], Format([Clientes].[Telefone 2],"(00) 0000-0000") as [Telefone 2], Format([Clientes].[Telefone 3],"(00) 0000-0000") as [Telefone 3], Clientes.Segurado, [B]Format([Dados dos Condutores].[Data de Nascimento],"mmdd")[/B]
FROM (Clientes LEFT JOIN [Dados do Seguro] ON Clientes.[N do Cliente] = [Dados do Seguro].[N do Cliente]) LEFT JOIN [Dados dos Condutores] ON [Dados do Seguro].[N do Seguro] = [Dados dos Condutores].[N do Seguro]
WHERE (((DateSerial(Year(Date()),Month(Nz([Dados dos Condutores].[Data de Nascimento],0)),Day(Nz([Dados dos Condutores].[Data de Nascimento],0)))) Between Date() And DateAdd("m",1,Date())));

 

[DCrake]

Are you simply want to find out who's birthday is in the next month/30 days?

 

[douglasrac]

Are you simply want to find out who's birthday is in the next month/30 days?

YES! But organized by MM DD

 

[DCrake]

Place this function in a standard module

Public Function NextBirthday(AnyDate As Date) As Date

Dim D As Date

D = Format(Day(AnyDate) & "/" & Month(AnyDate) & "/" & Year(Date), "dd-mmm-yyyy")


If D < Date Then
    D = DateAdd("yyyy", 1, D)
End If

NextBirthday = D


End Function

Include this column in your query

nBirthday:NextBirthday([DateOfBirth])

Then use between ... And ... in your condition

 

[douglasrac]

Thank you. You are very helpful!