Parameter Now() virker ikke med ODBC

are you using a passthrough query? If so, the sql you use must comply with the SQL server your working with, for example Navision.

Otherwise try converting date() or now to a string and put that into the SQL string rather. So you end up with an actual dat in the SAQL rather than a name of a function.

Otherwise try converting date() or now() to a string and put that into the SQL string. So you end up with an actual date in the SQL rather than a name of a function

Hi Terry.

Thank you so much for your answer. Well I have never worked with passthrough before but actually when I use this I do not get an error for the date.

I though Wonder why passthrough is better?

I have always used the Query designer so I am not that good at SQL formula.

I just write "select * from Transaktion" in my passthrough and then I have to join my other Query to this passthrough Query instead of a linked table but it seems like the same Work and also performance are not better.

Should I make my filter directly in the passthrough Query?:

select * from Transaktion
WHERE ((Transaktion.Dato)>=Now())

Kan I make my SQL with join to serveral other tables, filter etc. directly in the passthrough Query?

I tryed to copy this into the passthrough Query but that did not Work:

INSERT INTO tTransaktion ( Nøgle, Bonnr_, Butiksnr_, Kasseterminalnr_, Transaktionsnr_, Transaktionsart, [Ekspedient ID], Fornavn, Dato, Tid, Nettobeløb, Kostbeløb, Bruttobeløb, Betaling, Rabatbeløb, Totalrabat, [Antal varer], [Trans_ salgs-/bet_-diff_], [Indtægts-/udgiftsbeløb], Debitornr_, Navn, Debitorbogføringsgruppe, Poststatus, Opgørelseskode, Kundeattentionnr_, [GRIPS Sequence], [GRIPS Terminal], Kontoudtogsnr_, Bogføringsstatus, Md, År, [S-år] ) IN 'R:\Database\Transaktioner.mdb'
SELECT [Bonnr_] & "-" & [Transaktionsnr_] AS Nøgle, Transaktion_1.Bonnr_, Transaktion_1.Butiksnr_, Transaktion_1.Kasseterminalnr_, Transaktion_1.Transaktionsnr_, Transaktion_1.Transaktionsart, Transaktion_1.[Ekspedient ID], Ekspedient_1.Fornavn, Transaktion_1.Dato, Transaktion_1.Tid, Transaktion_1.Nettobeløb, Transaktion_1.Kostbeløb, Transaktion_1.Bruttobeløb, Transaktion_1.Betaling, Transaktion_1.Rabatbeløb, Transaktion_1.Totalrabat, Transaktion_1.[Antal varer], Transaktion_1.[Trans_ salgs-/bet_-diff_], Transaktion_1.[Indtægts-/udgiftsbeløb], Transaktion_1.Debitornr_, tDebitor.Navn, tDebitor.Debitorbogføringsgruppe, Transaktion_1.Poststatus, Transaktion_1.Opgørelseskode, Transaktion_1.Kundeattentionnr_, Transaktion_1.[GRIPS Sequence], Transaktion_1.[GRIPS Terminal], Transaktion_1.Kontoudtogsnr_, Transaktion_1.Bogføringsstatus, Month([Transaktion_1].[dato]) AS Md, Year([Transaktion_1].[Dato]) AS År, Årstal.RegnskabsÅr AS [S-år]
FROM ((Transaktion AS Transaktion_1 LEFT JOIN Årstal ON Transaktion_1.Dato = Årstal.Dato) LEFT JOIN tDebitor ON Transaktion_1.Debitornr_ = tDebitor.Nummer) LEFT JOIN Ekspedient AS Ekspedient_1 ON Transaktion_1.[Ekspedient ID] = Ekspedient_1.Id
WHERE (((Transaktion_1.Dato)>=Now()-5) AND ((Transaktion_1.Poststatus)<>"Annulleret" And (Transaktion_1.Poststatus)<>"Træning"));

Hi
"I though Wonder why passthrough is better?"

Passthrough should execute MUCH faster because the SQL is executed on the server and not on the local PC inside Access.

I did notice you mentioned Navision and not knowing anything at all about that dB i cant be sure if there will be any benefits at all using Passthrough queries, but I'd be surprised if there wasn't.

Using Passthrough means that you cant use the query designer (in Access), you have to write the SQL manually :-(
But if there is a query designer in Navision then you could use that, then copy SQL back into Access.

Not easy to see what the problem could be as I dont have your dB, but I would try getting the SELECT working first.

Try replacing the Date() or Now() functions with an actually date, so you can confirm that this is the problem. If it is then you will need to build th eSQL in code and either execute it from there, or make a queryDef (query) through code and execute the query.

Building the SQL in code allows you to replace the functions Date() and now() with the actual values these return and put them into the SQL string.

https://msdn.microsoft.com/en-us/library/dd627355(v=office.12).aspx

Well it worked with the date - I just join my queries to the passthrough instead of a linked table so please place an answer.

I run all my queries from a server - not the Navision server though - so perhaps therefore the performance are not affected.

I also filter my queries so perhaps the filter affects that the link to the Navision tables are okay quick.

You say "I also filter my queries so perhaps ..."

Not sure what you mean, using the where in SQL also filters the result. So if your getting a result with the SQL and then using for example a filter in an Access form is defeating the object in using passthrough queries to place the workload on the SQL server.

Anyway, good to hear you got something working.

Thanks

Hi Terry.

Can you help with this also?

I have this SQL:

select * from Transaktion
WHERE ((Transaktion.Dato)>=Now()-15)

But it is very slow as I think it runs through all "Dato" to find the top 15 dates.

If I use this SQL it is a lot quicker but it is not dynamic:

select * from Transaktion
WHERE (Transaktion.Transaktionsnr_)>=80000

How should the SQL look like if I want it to take the 5000 higest "Transaktionsnr"??

Please note that several transaktions are added everyday so I need it to be dynamic to it takes the last/newest 5000 rows

I have tried with no luck:

select * from Transaktion
WHERE (Transaktion.Transaktionsnr_)>= MAX(Transaktion.Transaktionsnr_) - 5000

Hope you can help - thx.

Mhhh perhaps it have to run through all rows to find the last rows so I cannot speed it up dynamically???

Hi
I'll try and help you with these questions but you should place a new question so that other users can participate also.

Its never a good idea to use functions in your SQL because the function is called for all rows in table.

Also, fields you use as search criteria should be indexed.

Try this, it may work.

Change SQL to
select * from Transaktion
WHERE ((Transaktion.Dato)>=[Enter date])

When you run the query you will be prompted to enter a date. Just enter todays date first subtracting 15 days from it.
If its faster and this method is acceptable then great, then otherwise you could try having a form with a field where you enter the date.. Then alter query so that it uses this field in place of the parameter [Enter date].
Another method is to build the SQL dynamically in code.

If I understand you next question regarding "the 5000 higest "Transaktionsnr" correctly.

You can use the TOP predicate to do this.
https://support.office.com/en-au/article/ALL-DISTINCT-DISTINCTROW-TOP-Predicates-24f2a47d-a803-4c7c-8e81-756fe298ce57