Where are the Parameters?

[selenau837]

Below is a query in SQL view that is driving me crazy.

When ran it ask for a StartDate, EndDate, StartDate, EndDate.

Can someone please look and see if they can determin where the criteria is coming in from. In design view there is no criteria set up to ask for dates. Also, there is no parameter set in the parameters box.

SELECT tblBooksAndContracts.intTrackingNumber, tblGroupInformation.strGroupName, tblGroupInformation.strGroupNumber, tblSystems.ysnSystemWork, tblSystems.dtmSystemWorkComplete, tblGroupInformation.dtmDateMembershipReceived, tblGroupInformation.strRegion, tblBooksAndContracts.ysnNeedBook, tblBooksAndContracts.ysnNeedContract, tblBooksAndContracts.intBookAndContractCarveOut, CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent]) AS [Book Complete], tblBooksAndContracts.dtmContractDistributedToMarketing AS [Contract Complete], CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing]) AS [B/C Complete], ([tblGroupInformation.dtmDateMembershipReceived]+[tblBooksAndContracts].[intBookAndContractCarveOut]+30) AS ECD, CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing],[tblSystems].[dtmSystemWorkComplete],[tblGroupInformation].[dtmDateMembershipReceived]) AS [Master Complete Date], tblGroupInformation.dtmEffectiveDate, tblGroupInformation.strRegion, tblGroupInformation.strNRC
FROM (tblBooksAndContracts INNER JOIN tblSystems ON tblBooksAndContracts.intTrackingNumber = tblSystems.intTrackingNumber) INNER JOIN tblGroupInformation ON (tblSystems.intTrackingNumber = tblGroupInformation.intTrackingNumber) AND (tblBooksAndContracts.intTrackingNumber = tblGroupInformation.intTrackingNumber)
WHERE (((EntryIsComplete([ysnSystemWork],[dtmSystemWorkComplete],[ysnNeedIDCard],[dtmMailIDCards],[ysnNeedBook],[ysnNeedContract],[ysnNeedDraft],[ysnNeedFlyer],[ysnBookAndContractComplete]))=Yes) AND ((IsBetween([startDate],[endDate],[tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing],[tblSystems].[dtmSystemWorkComplete],[dtmMailIDCards],[tblGroupInformation].[dtmDateMembershipReceived]))=Yes));

As well as when it does run, and you enter the span dates, if the field is blank it inputs 12:00 am in the field and includes it in the query. I checked the tables and it is not set up to input 12:00 am as a default value, nor is it stored as 12:00 am in the tables.

I am at my wits end here and any help/advice would be helpful.

If this does't make sense let me know and I will try and explain further.

Thanks in advance!

*please disregard the grammer, I am typing this fast before I head out to get the kids..a mommies job is never done!*

 

[edtab]

AND ((IsBetween([startDate],[endDate],[tblBooksAndContracts].[tblSystems].[dtmSystemWorkComplete],[dtmMailIDCards],[tblGroupInformation].[dtmDateMembershipReceived]))=Yes));

It appears to be coming from the above. See your WHERE clause.

 

[selenau837]

Yes, I saw that; however, that does not explain why it ask for the date span twice.

I am still completely stumped with this frakin' query. That area's management didn't say whether it has been broken this whole time or not. If it was, why didn't they get the dude who made it a year ago to fix it. Grrrr!!

Ok, tantrum over with.

If anyone has any other ideas I would be greatly indebted to you! *smiles*

 

[Mile-O]

when it does run, and you enter the span dates, if the field is blank it inputs 12:00 am in the field and includes it in the query. I checked the tables and it is not set up to input 12:00 am as a default value, nor is it stored as 12:00 am in the tables.

Well, the parameter requires a date and if you don't put one then you are effectively putting in 0, and 0 represents 30th December, 1899 at 00:00:00.

And if it's asking for the same date twice it's because the Parameter has not been properly defined and is, instead, only used in the SQL (i.e. no PARAMETERS claus) and has to be evaluated for each instance of use to fill the values.

 

[selenau837]

Well, the parameter requires a date and if you don't put one then you are effectively putting in 0, and 0 represents 30th December, 1899 at 00:00:00.

And if it's asking for the same date twice it's because the Parameter has not been properly defined and is, instead, only used in the SQL (i.e. no PARAMETERS claus) and has to be evaluated for each instance of use to fill the values.

In the table the query is ran from, those fields are blank. They do not have 12:00 as a default setting. That is why I am soo confused.

That means the class mod is broken?

 

[Mile-O]

Can you upload a stripped down version of what you have?

 

[selenau837]

What exactly do you want? The database with all the info removed?

 

[Mile-O]

Wait just now...

What parameter are you being asked for?

Is there anything in the EntryIsComplete() function, whereever it may be, that is required?

 

[selenau837]

Here it is with the tables, query and all the mods for this database.

I removed the data, but you can see how it is set up.



Ok, trying to upload once more.

 

[selenau837]

B/CComplete Field is the one that spits out 12:00 if that field is blank in the table.

 

[selenau837]

I give up, after about 5 hours and still nothing!!!!! I am ready to 'accidently' delete this blasted query.

I asked that area's management if it ever worked, and they just looked at me with the blank stare.

Anywho..... anyone else with ideas? If not, I forsee a tragic accident happening in the future. . That will require a NEW one, that isn't so dag-gum difficult. Grrrr!!

Access and PMS! Not a good combo! LOL!

Thank you,
selena