Hi,
Ive been using the following line that works fine when the query isnt linked to the front page
Set rs = CurrentDb.OpenRecordset("UkMailDataNotDelivered")
but now i need it to use 6 details from the home form.
I think the 2 options are to load the query with the 6 variables or add them to the query first.
i couldnt get this to work thou, so any advice is appreciated
Set rs = CurrentDb.OpenRecordset("SELECT UkMailData.ID, UkMailData.Status, UkMailData.Deleted, UkMailData.Status2, " & _
"UkMailData.AccNumber, UkMailData.Customer, UkMailData.Address1, UkMailData.Address2, " & _
"UkMailData.[Town/City], UkMailData.County, UkMailData.PostCode, UkMailData.ParcelRef, " & _
"UkMailData.CustomerRef, UkMailData.DateSent, UkMailData.FileLocation, UkMailData.Reason " & _
"FROM UkMailData " & _
"WHERE (((UkMailData.Status) Is Null) AND ((UkMailData.Deleted)='"No"') AND ((UkMailData.AccNumber) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text124] & '"*"') AND ((UkMailData.Customer) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text126] & '"*"') AND ((UkMailData.PostCode) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text125] & '"*"') AND ((UkMailData.ParcelRef) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text127] & '"*"') AND ((UkMailData.DateSent)>=[Forms]![MAIN MENU]![Text129] " & _
"And (UkMailData.DateSent)<=[Forms]![MAIN MENU]![Text128])) OR (((UkMailData.Status) " & _
"Not Like '"Delivered"' And (UkMailData.Status) Not Like '"Delivery Rearranged*"') " & _
"AND ((UkMailData.Deleted)='"No"') AND ((UkMailData.AccNumber) Like '"*"' & [Forms]![MAIN MENU]![Text124] & '"*"') " & _
"AND ((UkMailData.Customer) Like '"*"' & [Forms]![MAIN MENU]![Text126] & '"*"') AND ((UkMailData.PostCode) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text125] & '"*"') AND ((UkMailData.ParcelRef) Like '"*"' & [Forms]![MAIN MENU]![Text127] " & _
"& "*") AND ((UkMailData.DateSent)>=[Forms]![MAIN MENU]![Text129] And " & _
"(UkMailData.DateSent)<=[Forms]![MAIN MENU]![Text128]));")
Ive been using the following line that works fine when the query isnt linked to the front page
Set rs = CurrentDb.OpenRecordset("UkMailDataNotDelivered")
but now i need it to use 6 details from the home form.
I think the 2 options are to load the query with the 6 variables or add them to the query first.
i couldnt get this to work thou, so any advice is appreciated
Set rs = CurrentDb.OpenRecordset("SELECT UkMailData.ID, UkMailData.Status, UkMailData.Deleted, UkMailData.Status2, " & _
"UkMailData.AccNumber, UkMailData.Customer, UkMailData.Address1, UkMailData.Address2, " & _
"UkMailData.[Town/City], UkMailData.County, UkMailData.PostCode, UkMailData.ParcelRef, " & _
"UkMailData.CustomerRef, UkMailData.DateSent, UkMailData.FileLocation, UkMailData.Reason " & _
"FROM UkMailData " & _
"WHERE (((UkMailData.Status) Is Null) AND ((UkMailData.Deleted)='"No"') AND ((UkMailData.AccNumber) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text124] & '"*"') AND ((UkMailData.Customer) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text126] & '"*"') AND ((UkMailData.PostCode) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text125] & '"*"') AND ((UkMailData.ParcelRef) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text127] & '"*"') AND ((UkMailData.DateSent)>=[Forms]![MAIN MENU]![Text129] " & _
"And (UkMailData.DateSent)<=[Forms]![MAIN MENU]![Text128])) OR (((UkMailData.Status) " & _
"Not Like '"Delivered"' And (UkMailData.Status) Not Like '"Delivery Rearranged*"') " & _
"AND ((UkMailData.Deleted)='"No"') AND ((UkMailData.AccNumber) Like '"*"' & [Forms]![MAIN MENU]![Text124] & '"*"') " & _
"AND ((UkMailData.Customer) Like '"*"' & [Forms]![MAIN MENU]![Text126] & '"*"') AND ((UkMailData.PostCode) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text125] & '"*"') AND ((UkMailData.ParcelRef) Like '"*"' & [Forms]![MAIN MENU]![Text127] " & _
"& "*") AND ((UkMailData.DateSent)>=[Forms]![MAIN MENU]![Text129] And " & _
"(UkMailData.DateSent)<=[Forms]![MAIN MENU]![Text128]));")