Access to SQL

[Pauldohert]

I have a field in an access query


Overdue: [InvoiceDate]+[PaymentTerms]<Date()


So this returns -1 , 0 or null (if InvoiceDate is null)


How do I make this in SQL?

Ta

 

[mhartman]

Hello:

In your query if it does what you want, Select View, Sql View on the menu. That is the raw Sql statement for your query.

Regards
Mark

 

[SQL_Hell]

Hi


The SQL server equivalent for date() is GETDATE()

So...


[InvoiceDate]+[PaymentTerms]<GetDate()


I hope this is what you meant

 

[Pauldohert]

Thanks both of you -


I can't get the sntax to enter the expression on a SQL server view.

(I am using GetDate() instead of date)


What I have is DateAdd(Day,PaymentTerms,Invoicedate) - This bit works correctly

then if I try <= GetDate() I get invalid or miising expression.

Thanks

 

[SQL_Hell]

DateAdd(Day,PaymentTerms,Invoicedate) <= DateAdd(day,0,getdate())

 

[Pauldohert]

No - same problem


I am adding as above in the "Column" of the view design window and Overdue in the Alias.

 

[Pauldohert]

A similat problem would be how do I get


Overdue: 1 = 1 --- Obviuosly in an access query this comes out as -1


as a field in a view?

Ta

 

[SQL_Hell]

No - same problem


I am adding as above in the "Column" of the view design window and Overdue in the Alias.

Are you sure? I tested this and it works fine. Maybe its a problem with your data are you sure all the values in column PaymentTerms are integers?

 

[SQL_Hell]

A similat problem would be how do I get


Overdue: 1 = 1 --- Obviuosly in an access query this comes out as -1


as a field in a view?

Ta

I'm sorry you need to explain this better, I have no idea what you are talking about here

 

[Pauldohert]

I can get the result of DateAdd(Day,PaymentTerms,Invoicedate) in a views field similarly I can get th result of GetDate() in a views field. I can also get the result of 1 in a views field.

I cannot get

DateAdd(Day,PaymentTerms,Invoicedate) <= GetDate()

or

1 = 1

as the result of a views field.

In access its simple as outlined in the orignial post - but I can't seem to get it in a view.

 

[SQL_Hell]

just go into sql view and set it there

you will have something like

create view vDate
select * from table
where DateAdd(Day,PaymentTerms,Invoicedate) <= DateAdd(day,0,getdate())

Stop using the access query designer all together and start using sql server query analyzer

 

[Pauldohert]

This may be the problem - I don't want it as a where criteria - I actually want to output the result of

DateAdd(Day,PaymentTerms,Invoicedate) <= DateAdd(day,0,getdate())

as a field in the view -

as 1, 0 or NULL

 

[Pauldohert]

Stop using the access query designer all together and start using sql server query analyzer

Eh you are confusing me now!


I am using acces query designer when in access and enterprise manager "Deign View" when in SQL.

 

[pdx_man]

Explicitly define it:

CASE WHEN [InvoiceDate]+[PaymentTerms]<GETDate() THEN 1
WHEN [InvoiceDate]+[PaymentTerms]>=GETDate() THEN 0
ELSE NULL
END AS YourField

 

[Pauldohert]

Cheers - Thats it!