Queries and Now()

Hi

Using Access 2007.

I have a database in mdb format that records the information of customer 
sales on  a web site. In the OrderDate field I am using Now() to record the 
date and time the order is placed.

I also have a query which displays only currently open orders.

When I view either the table or query data locally within Access the 
date/time information of each order displays correctly.

Likewise, when I view the table data online via an ASP VB web page, the 
date/time information of each order displays correctly

However, when I view query data online via an ASP VB web page, the date/time 
information of each order displays as the date/time of the most recent 
order.

Why is this???

And how can I get the correct data to display? (I realize that I can use the 
table data directly by filtering the data in ASP, but would rather use the 
query.)

(On a similar note, I've noticed that a query that is sorted by a randomly 
generated number always sorts records randomly within Access but the records 
always appear in the same order when the query is viewed online through an 
ASP VB page.)

Thanks,

Craig 

0
Craig
2/1/2008 11:43:25 PM
access.queries 6343 articles. 1 followers. Follow

9 Replies
567 Views

Similar Articles

[PageSpeed] 49

"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:%23NVMBxSZIHA.5784@TK2MSFTNGP03.phx.gbl...
> Hi
>
> Using Access 2007.
>
> I have a database in mdb format that records the information of customer 
> sales on  a web site. In the OrderDate field I am using Now() to record 
> the date and time the order is placed.
>
> I also have a query which displays only currently open orders.
>
> When I view either the table or query data locally within Access the 
> date/time information of each order displays correctly.
>
> Likewise, when I view the table data online via an ASP VB web page, the 
> date/time information of each order displays correctly
>
> However, when I view query data online via an ASP VB web page, the 
> date/time information of each order displays as the date/time of the most 
> recent order.
>
> Why is this???

What is the code? 


0
Amy
2/1/2008 11:51:16 PM
What code are you referring to? On the ASP page?


"Amy Blankenship" <Amy_nospam@magnoliamultimedia.com> wrote in message 
news:uSBQ81SZIHA.5208@TK2MSFTNGP04.phx.gbl...
>
> "Craig" <msaccess@howardhowardfine.com> wrote in message 
> news:%23NVMBxSZIHA.5784@TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> Using Access 2007.
>>
>> I have a database in mdb format that records the information of customer 
>> sales on  a web site. In the OrderDate field I am using Now() to record 
>> the date and time the order is placed.
>>
>> I also have a query which displays only currently open orders.
>>
>> When I view either the table or query data locally within Access the 
>> date/time information of each order displays correctly.
>>
>> Likewise, when I view the table data online via an ASP VB web page, the 
>> date/time information of each order displays correctly
>>
>> However, when I view query data online via an ASP VB web page, the 
>> date/time information of each order displays as the date/time of the most 
>> recent order.
>>
>> Why is this???
>
> What is the code?
> 

0
Craig
2/6/2008 4:00:03 PM
I suspect your ASP implementation optimizes something it should not. It may 
also be version dependant, such as ASP.Net 3.5  or previous versions. Sure, 
that sounds like I try to send the problem to ASP, but if the query behaves 
correctly within Access... Can you try to get the data from, say, Excel, or 
from MS SQL Server, or from a dataview tool from C#, to see if it get as 
Access, or as your ASP implementation shows it, letting Excel (MS SLQ 
Server, C#) determines who is the source of the problem, kind of ... :-)



Vanderghast, Access MVP


"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:%23NVMBxSZIHA.5784@TK2MSFTNGP03.phx.gbl...
> Hi
>
> Using Access 2007.
>
> I have a database in mdb format that records the information of customer 
> sales on  a web site. In the OrderDate field I am using Now() to record 
> the date and time the order is placed.
>
> I also have a query which displays only currently open orders.
>
> When I view either the table or query data locally within Access the 
> date/time information of each order displays correctly.
>
> Likewise, when I view the table data online via an ASP VB web page, the 
> date/time information of each order displays correctly
>
> However, when I view query data online via an ASP VB web page, the 
> date/time information of each order displays as the date/time of the most 
> recent order.
>
> Why is this???
>
> And how can I get the correct data to display? (I realize that I can use 
> the table data directly by filtering the data in ASP, but would rather use 
> the query.)
>
> (On a similar note, I've noticed that a query that is sorted by a randomly 
> generated number always sorts records randomly within Access but the 
> records always appear in the same order when the query is viewed online 
> through an ASP VB page.)
>
> Thanks,
>
> Craig 


0
Michel
2/6/2008 4:24:35 PM
Could you say that again in English?  ;-)


"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message 
news:ug32N0NaIHA.1204@TK2MSFTNGP03.phx.gbl...
>I suspect your ASP implementation optimizes something it should not. It may 
>also be version dependant, such as ASP.Net 3.5  or previous versions. Sure, 
>that sounds like I try to send the problem to ASP, but if the query behaves 
>correctly within Access... Can you try to get the data from, say, Excel, or 
>from MS SQL Server, or from a dataview tool from C#, to see if it get as 
>Access, or as your ASP implementation shows it, letting Excel (MS SLQ 
>Server, C#) determines who is the source of the problem, kind of ... :-)
>
>
>
> Vanderghast, Access MVP
>
>
> "Craig" <msaccess@howardhowardfine.com> wrote in message 
> news:%23NVMBxSZIHA.5784@TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> Using Access 2007.
>>
>> I have a database in mdb format that records the information of customer 
>> sales on  a web site. In the OrderDate field I am using Now() to record 
>> the date and time the order is placed.
>>
>> I also have a query which displays only currently open orders.
>>
>> When I view either the table or query data locally within Access the 
>> date/time information of each order displays correctly.
>>
>> Likewise, when I view the table data online via an ASP VB web page, the 
>> date/time information of each order displays correctly
>>
>> However, when I view query data online via an ASP VB web page, the 
>> date/time information of each order displays as the date/time of the most 
>> recent order.
>>
>> Why is this???
>>
>> And how can I get the correct data to display? (I realize that I can use 
>> the table data directly by filtering the data in ASP, but would rather 
>> use the query.)
>>
>> (On a similar note, I've noticed that a query that is sorted by a 
>> randomly generated number always sorts records randomly within Access but 
>> the records always appear in the same order when the query is viewed 
>> online through an ASP VB page.)
>>
>> Thanks,
>>
>> Craig
>
> 

0
Craig
2/6/2008 9:23:35 PM
"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:ObVC4kNaIHA.5160@TK2MSFTNGP05.phx.gbl...
> What code? In the ASP page?
>
> It's Now()  in the Access table for the OrderDate field.

By the time you're running your query to display the data, it should already 
have been stored.  So, yes, your ASP code. 


0
Amy
2/6/2008 10:02:48 PM
"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:uA3jNaQaIHA.5768@TK2MSFTNGP03.phx.gbl...
> Could you say that again in English?  ;-)
>
>
> "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message 
> news:ug32N0NaIHA.1204@TK2MSFTNGP03.phx.gbl...
>>I suspect your ASP implementation optimizes something it should not. It 
>>may also be version dependant, such as ASP.Net 3.5  or previous versions. 
>>Sure, that sounds like I try to send the problem to ASP, but if the query 
>>behaves correctly within Access... Can you try to get the data from, say, 
>>Excel, or from MS SQL Server, or from a dataview tool from C#, to see if 
>>it get as Access, or as your ASP implementation shows it, letting Excel 
>>(MS SLQ Server, C#) determines who is the source of the problem, kind of 
>>... :-)

I think what he is saying is that the problem is probably in your ASP code 
and not the query.  So probably in the loop where you think you've changed 
what you're looking at, you're changing it for everything but the 
misbehaving field.  So maybe you store that field's value above the loop, 
and you're always retrieving the same value. 


0
Amy
2/6/2008 10:05:38 PM
Exactly, thanks. In addition, ASP settings themselves may possibly cache 
some data it perceives as 'constant', avoiding a round trip to the server. 
That is why I suggest to use another tool, to see the data, for yourself, 
not to write your application, no, but to determine, hopefully, if the 
problem is within Access, or with ASP. If the third tool shows data as in 
Access, then the ASP code you use is probably the problem, not the Access 
query that you use.



Vanderghast, Access MVP

"Amy Blankenship" <Amy_nospam@magnoliamultimedia.com> wrote in message 
news:%23z0uQyQaIHA.484@TK2MSFTNGP06.phx.gbl...
>
> "Craig" <msaccess@howardhowardfine.com> wrote in message 
> news:uA3jNaQaIHA.5768@TK2MSFTNGP03.phx.gbl...
>> Could you say that again in English?  ;-)
>>
>>
>> "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message 
>> news:ug32N0NaIHA.1204@TK2MSFTNGP03.phx.gbl...
>>>I suspect your ASP implementation optimizes something it should not. It 
>>>may also be version dependant, such as ASP.Net 3.5  or previous versions. 
>>>Sure, that sounds like I try to send the problem to ASP, but if the query 
>>>behaves correctly within Access... Can you try to get the data from, say, 
>>>Excel, or from MS SQL Server, or from a dataview tool from C#, to see if 
>>>it get as Access, or as your ASP implementation shows it, letting Excel 
>>>(MS SLQ Server, C#) determines who is the source of the problem, kind of 
>>>... :-)
>
> I think what he is saying is that the problem is probably in your ASP code 
> and not the query.  So probably in the loop where you think you've changed 
> what you're looking at, you're changing it for everything but the 
> misbehaving field.  So maybe you store that field's value above the loop, 
> and you're always retrieving the same value.
> 


0
Michel
2/7/2008 2:35:11 PM
Could you put that a little more clearly, please?

Craig

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message 
news:ug32N0NaIHA.1204@TK2MSFTNGP03.phx.gbl...
>I suspect your ASP implementation optimizes something it should not. It may 
>also be version dependant, such as ASP.Net 3.5  or previous versions. Sure, 
>that sounds like I try to send the problem to ASP, but if the query behaves 
>correctly within Access... Can you try to get the data from, say, Excel, or 
>from MS SQL Server, or from a dataview tool from C#, to see if it get as 
>Access, or as your ASP implementation shows it, letting Excel (MS SLQ 
>Server, C#) determines who is the source of the problem, kind of ... :-)
>
>
>
> Vanderghast, Access MVP
>
>
> "Craig" <msaccess@howardhowardfine.com> wrote in message 
> news:%23NVMBxSZIHA.5784@TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> Using Access 2007.
>>
>> I have a database in mdb format that records the information of customer 
>> sales on  a web site. In the OrderDate field I am using Now() to record 
>> the date and time the order is placed.
>>
>> I also have a query which displays only currently open orders.
>>
>> When I view either the table or query data locally within Access the 
>> date/time information of each order displays correctly.
>>
>> Likewise, when I view the table data online via an ASP VB web page, the 
>> date/time information of each order displays correctly
>>
>> However, when I view query data online via an ASP VB web page, the 
>> date/time information of each order displays as the date/time of the most 
>> recent order.
>>
>> Why is this???
>>
>> And how can I get the correct data to display? (I realize that I can use 
>> the table data directly by filtering the data in ASP, but would rather 
>> use the query.)
>>
>> (On a similar note, I've noticed that a query that is sorted by a 
>> randomly generated number always sorts records randomly within Access but 
>> the records always appear in the same order when the query is viewed 
>> online through an ASP VB page.)
>>
>> Thanks,
>>
>> Craig
>
> 

0
Craig
2/14/2008 5:38:29 AM
"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:e4ORWvsbIHA.5364@TK2MSFTNGP04.phx.gbl...
> Could you put that a little more clearly, please?

If you post your ASP code, we can look at it and probably tell you exactly 
where the problem is. 


0
Amy
2/14/2008 3:48:15 PM
Reply:

Similar Artilces:

Query Problem 03-27-10
Hi, I have 2 tables, tbBaby and tbVaccineInjection. tbBaby stores the babies' personal data like Name, BirthDate etc., tbVaccine stores VaccineName, DoseNumber (1st, 2nd, 3rd, etc.) and InjectionDate, etc. These 2 tables are one to many in relation, because each baby may have several vaccinations on different dates. I'd like to build a query to retrieve babies with their most recent injection date, i.e. only one record for each baby with latest injection date. my query is as follow SELECT tbBaby.*, tbVaccine.* FROM tbBaby INNER JOIN tbVaccine ON tbBaby.ID=tbVaccine.B...

Append query bloat
I have an access database where I use an append query quite often. I will use this same query over and over by changing the table I'm appending from, then I save the query. During the save process, the database bloats from ~50KB to ~150KB. Our company recently upgraded to 2007 and that is when I noticed the problem. Is there a way to stop this from happening? I can manage it by compacting and repairing after each save, but recently I added 4 tables in one day, so this was a pain. Thanks Don't even worry about it. An Access file can be up to 2 GB in size. 150 KB ...

Does SQL Run Behind Queries?
Access 2007 When I run a query does Access, "go away" and run the SQL for what I see in the GUI? If the answer is, "yes" is there anything that I can switch on to see the SQL that it is running? What I would like to do, if it is possible, is to see the query in the GUI and, at the same time, see the SQL that is being used to select the data from the database; that way I can understand things (exactly what is happening) more clearly and more quickly. TIA for any replies. trip_to_tokyo wrote: > Access 2007 > > When I run a query does...

Database Query #4
I have created a data table in a worksheet and named it “data”. Then I created a separate worksheet, in the same workbook, and created a database query of the data table, by using Data…Import External Data…New Database Query…Excel Files* and defining the database name by browsing to the same workbook. The query worked fine. However, when I moved the workbook (which included the data source) to a different folder and tried to refresh the database query, I got the following error message: “[Microsoft] [ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘data’...

DNS clients Does not query the secondary DNS
Hi, I have 2 w2003 Dcs - both DNS servers Primary and secondary. I have Clients running XP static Ip and in both DNS servers IP is configured as Primary and Secondary DNS. My Primary DNS server went down, all clients are not quering the secondary! using NSLOOKUP it showed that clients are still querying the primary with a tomeout error !! all name resoultiond stoped in the organization! any idea? tx NsLookup does not automatically fall through to alternate DNS servers, it is not a good way to test server redundancy. If you ping a host, which will use the DNS Cl...

Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

How to use colmn headings in paramter query
Sorry if this has been asked before, but I am not able to locate it if it has. But here is my delima: I need to be able to select the salesperson sales based on the month (1-12) from table 1. I* can create a parameter query, but am not sure hwo to query onlya certain month. table1 ID salesperson 1 2 3 4 5 6 7 8 9 10 11 12 1 joe 12 0 0 2 12 0 1 1 5 7 8 10 2 steve 2 5 6 18 0 0 2 3 4 11 12 1 select * from table1 where salesperson =joe and .... that is where I get stuck. I need to be able to select a column he...

I fixed the date and time and the time zone. Now reply me please
Hi, I have some questions in outlook 2003, can anyone help me? 1-how can i in the appointment type a subject with special format (red, bold..) 2-whent a task is 100% completed, how can be deleted automatically. 3- I need to filter only the available time on a specific day. ex if i have 2 appointments today on a specific time, what i need to see is the available time for other new appointments. 4-If I have a lot of appointments on a specific date that are colored with labels and have a specific show time as (busy,tentative), when i go to print this calendar with the "calendar detai...

Extracting invoice data from GP 9 using a SQL query (ODBC)
I'm working to extract invoice detail information from a client's GP 9 MSSQL database, with the goal of being able to mock up their invoices in external systems. Just to keep things interesting, they aren't using GP to it's maximum ability - quotes are generated from another software package, and when converted to a sale, minimal data is entered into GP and processed (such as shipping address, job name, job number, PO number, and occasionally a line it of what appears to be free-form text. I have hard copies of several invoices, various web resources, and access to t...

Query Does Not Count "2" Records
Good Evening Everyone... I have a bit of a logical puzzle that I need to solve. I have various queries which have various criteria for them. One query involves retirees or spouses being under the age of 65 and the other involves retirees or spouses being over the age of 65. Both the retiree and spouse are listen in the same records and on one line of the master table and queries. The situation that I am running into is that I can have a retiree who is over 65 and a spouse under 65 and the record comes up twice in the two different queries b/c it meets the criteria, which is perfect. What...

Web Query Help...
If I try and use a web query with the following site http://www.sportsline.com/mlb/stats/playersort/regularseason/yearly/MLB/P I get a message that says the web query returned no data. I click on the arrow right by player and it highlights all the players names and their stats. Anyone know why it doesn't import the data into excel? Is there too much data? Thanks Works fine for me, although one have to do some editing since it imports some trash as well (I would import it, then select the table itself from the import and copy and paste into a new sheet, then import the next 50 and...

Fetch XML to Query Expression
I'm trying to convert the following Fetch XML statement to a Query Expression to post using the RetrieveMultiple webservice: <fetch mapping="logical"> <entity name="role"> <attribute name="name" /> <link-entity name="systemuserroles" from="roleid" to="roleid"> <filter> <condition attribute="systemuserid" operator="eq-userid" /> </filter> </link-entity> </entity> </fetch> I haven't been able to find an documenta...

Query Output
Help, I am working with make table queries. I have a A make table query and a B make table query each with various information that the query has preformed. I am trying to get the following output into a text file. A Make Table Information B Make Table Information A Make Table Information B Make Table Information etc. There are over 300 items in each table. Each A Corresponds with a B. I need to keep the 2 records separate for where i have to upload them. I tried having a common identifier for the two tables and creating a big query with all the information on, but i ...

Included AfxImpl.h
I'm trying to implement a change of defaults in the Print Setup Dialog according to KB Article 166130. In order to define AfxGetFileTitle and afxData (used in ViewPrnt.cpp) I had to include AfxImpl.h, which was found in the MFC Source directory. Now there is a link error: unresolved external symbol "class CProcessLocal<class _AFX_WIN_STATE> _afxWinState" (?_afxWinState@@3V?$CProcessLocal@V_AFX_WIN_STATE@@@@A) I found CProcessLocal and _AFX_WIN_STATE defined in AFXSTAT_.H and AFXTLS_.H, but including them didn't help. Any ideas? Thanks, Rick ...

Query-Based E-mail Groups
I have created query-based email group that contains 1,600 users. When I send an email to that group, about 350 users don't get the message. I as the owner of the group get an undeliverable stating: The e-mail system limits the number of recipients that can be addressed in a single message. Send the message multiple times to fewer recipients. For all 350 users. So I check my "Message Delivery Properties" in ESM. I have my "Recipient Limits" set to 2,000. I double checked the query-based email group to make sure it was under 2,000 recipients and ...

Suggestions for table or query to track movement
I have a table for employees that includes the usual description fields for employees. This is one of my master tables with the employee ID being the primary key. Then I have another table that stores all employee hours that I add on to monthly via an import from Excel (emplID is foreign key). I want to keep track of all of the employees that are new or terminate. I’d like Access to recognize if an employee is missing or new and later be able to create a report for recent new hires or terminations. I may be expecting too much and if so please give me your opinion on this. ...

Calculating the Difference in a Query
I need to calculate the difference between two fields in a query. The query is called “qryRemote” and within the query are the fields “10-8Time” and “10- 97Time”. The data entered into these fields are in the format “0000” through to “2400”. I need to subtract the “10-8Time from the “10-97Time” and then sum the results and display the result in a report. For example, after the query is run the results are: 10-8Time 10-97Time --------------------------------- 1307 1331 1858 1909 2018 2023 First the difference is determined: 10-8Time is subtracted form t...

Outlook 2000 Calendar Text Entry query. Help!
Husband and I are both retired and have never used Outlook before, even though it's installed on our home computer. Now that we have more time on our hands, we're using the Outlook monthly calendar: we print out a "blank" month at a time, pop the page into a ring bind folder, and then write (by hand) notes on the various days. We would like to be able to do this on-screen though, rather than with a pen! We're planning a day-by-day schedule for a holiday next February but we cannot work out how to "write" timetable notes into the squares for each separate...

Moving queries
hello! I need help! I am trying to copy a long list of queries from one access database to another. I tried "get external data", but it brought the actual data from the old database with the queries. Export will only allow me to move one query at a time... How do I move a whole list of queries? Thank you! Go to the other database and import them. "Amanda P" <Amanda P@discussions.microsoft.com> wrote in message news:74881BCB-09D3-44A6-9A06-198EB1486E69@microsoft.com... > hello! > I need help! > I am trying to copy a long list of queries from one ac...

Offset Formula Query
I am using an offset formula to bring back data. I am trying to bring back the cell next to the one that has a label that matches the file name e.g. the file name is Place and then column a contains a list of places and column b contains text about those places. I am using the following formula: =OFFSET(Data!A15,MATCH(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5),Data!$A$15:Data!$A$37,0),1) Where Data! refers to the sheet which contains the data and t...

Corruption, and now strange things happening ...
I've been working on a major form with several tabs and subforms for awhile now. My database "encountered an error" yesterday when running the form while testing and I was locked out. I followed several steps in a comprehensive list of things to try to open it that I found on this site. I ended up getting almost to the end of the list, which was to create a new blank db and import everything except the problem subform. I then imported a previous version of that form and started working on it again. Here is what I have found from testing: The problem all boiled down...

Query assistance 10-18-07
I have 2 tables and I am looking to create a query that will display the data only if it exists in both tables. I have been doing this in the past by running 2 unmatched queries, but I am assuming that there must be a faster, better way and I am hopeful that someone here could help. IF the data has to exist in both tables you could just join the tables on the relevant field(s). With an inner join the data would have to be in both tables to return any record. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore...

Fail to create IXSSO.Query object
I'm moving classic asp sites from Windows 2000 / IIS 5 to Windows 2008 R2 / IIS 7. * We have the "Indexing Service" started. * I have forced IIS to run application pool in 32 bit mode. * IXSSO.DLL is present in System32 directory. (dated 7/13/2009 it is from W2k8, I assume it is 64 bits) * Catalogs were created and looked OK. However, I am unable to create an ixsso.Query object. Get the following error message : Microsoft VBScript runtime error '800a01ad' ActiveX component can't create object /HC/ECO/Announcements/Headlines/SearchResul...

Update label and record query steps
Hi, I have a time consuming import code which has multiple steps. I amend the label caption to show which step the import is on but would like to show all steps and if possible place these steps in an archive table as the process is run by several users. At the moment I have: 1 - Preparing import...... which is replaced by 2 - Importing cust table 114,240 records (est. dur. 1 min)......... which is replaced by 3 - Importing sales table 842,600 records (est. dur. 4 mins)....... etc etc What I would like is: Query steps...... 18/02/2010 23:30:30 - Step 1 - Pre...

New column in query
I have the following columns in my query empid, courseA, datecompA, courseB, datecompB, courseC, datecompC. What I need is an extra column that will have an indicator Yes if any of the 2 dates(i.e., datecompA, datecompB, datecompC) have dates, blank if all 3 dates have dates and No if only of the dates has a date. Based on your description of the fields you have in your query, it sounds like you are working from ... a spreadsheet! Is this a query based on a "linked table" (linked to an Excel spreadsheet)? If not, and if this is an Access table, your database would benefit ...