Report only products <=ToDate

[iskov]

Hello Access-programmers!

It is my first time posting here, so bare with me if I do anything wrong.

I am currently working on a database for the company I am working for. The idea behind this is to store informations on products we have at customers around europe.
These products will have a servicecontract and I have therefore made a calculation of "LastService" + "MonthlyInterval" = "NextService" it looks like this in the Query:

LastService: IIf(IsNull([LastService]);[BisDatum];IIf(IsNull([MonthlyInterval]);[BisDatum];DateAdd("m";[MonthlyInterval];[LastService])))

This will ask me for the latest date the service should be on all of the products in my list and works very well, the report shows the products within the dates.

HOWEVER

I also have a searchfield with a connected button:

Private Sub Kommandoknap32_Click()
Dim strsearch As String
Dim strText As String
Dim Kunde As String

Kunde = Right$(Me.Filter, Len(Me.Filter) - 10)

If (Len(txtsearch.Value) > 0) Then
    strText = Me.txtsearch.Value
    strsearch = "SELECT * from ProduktListeQ where (KundeId like " & Kunde & " AND ((ProduktNavn like ""*" & strText & "*"") or (TypeNummer like ""*" & strText & "*"") or (HøjreVenstre like ""*" & strText & "*"") or (SerieNummer like ""*" & strText & "*"") or (IntervalMåneder like ""*" & strText & "*"") or (Kunden like ""*" & strText & "*"")))"
        Me.RecordSource = strsearch
Else
    strsearch = "SELECT * from ProduktListeQ where (KundeId like " & Kunde & ")"
    Me.RecordSource = strsearch
End If
End Sub

This might seem alittle messy but actually works fine, it allows me to search for information in each of the fields I have in my Form with listed products at the chosen customer.

However if I search for product "AGOL" and then press the reports button I want it to show ONLY "AGOL" products within the Date Scale I chose. Is this possible?

I hope you understand what I am trying to tell you. Else I will try to explain as well as I can!

Kind Regards,
Anders Iskov

 

[CJ_London]

you say

if I search for product "AGOL"

is product 'AGOL' the value entered in txtSearch?

What is Kunde? if it is a unique id then (because of the following AND) it will only return the product with that ID

 

[iskov]

Hello CJ

Correct, the txtsearch field is where I put my value, in this case "ProduktNavn" (ProductName) is my searchvalue.

Actually I am using a Database I was programming with another company in Germany, hence the confusion with Danish/Germany/English words. Kunde is basically:

"[KundeID]="

Which means it subtracts the letters and therefore I will be able to search for what is filtered for this specific Customer in this specific Form.

I hope it makes sense and thanks for your reply

 

[CJ_London]

sorry not very clear, but some thoughts

if 'Which means it subtracts the letters ' then

where (KundeId like " & Kunde & "

needs to be

where (KundeId like '" & Kunde & "'

 

[iskov]

The searchfunction works perfectly, however I cannot make my reportbutton firstly only show what I filtered for through my txtsearch field AND THEN ToDate

 

[CJ_London]

so you need a bit more criteria

.... AND myDate Between #" & format(me.startdate,"mm/dd/yyyy") & "# AND #" & format(me.enddate,"mm/dd/yyyy") & "#"

 

[iskov]

so you need a bit more criteria

.... AND myDate Between #" & format(me.startdate,"mm/dd/yyyy") & "# AND #" & format(me.enddate,"mm/dd/yyyy") & "#"

Hey CJ I am really sorry I didnt reply to you, I did not see the post :banghead:

It tells me when I plug this into the Criteria that the "expression is invalid" and then highlights the "#" & format(me.startdate,"dd/mm/yyyy") & "#"

 

[CJ_London]

Criteria that the "expression is invalid" and then highlights the "#" & format(me.startdate,"dd/mm/yyyy") & "#"

On the basis you will have used your name for 'startdate', I am unable to see what is wrong with my suggestion - can you post what you have actually got as code

 

[iskov]

On the basis you will have used your name for 'startdate', I am unable to see what is wrong with my suggestion - can you post what you have actually got as code

it highlights the part I talked about above

 

[CJ_London]

you need to change mydate, startdate and enddate to names which mean something to your query- I would expect mydate to be something in one of your tables and me.startdate and me.enddate will be parameters to your query - from your original post you were building this in VBA so these two were referring to controls on your form