1

This is specifically for MS-Access Web Databases (requires Sharepoint hosting) which has many limitations compared to their client counterparts, like no VBA, instead you get form macros and data macros to manage data.

I've run into a weird bug on one of my applications. I have a query used to check stock levels against a "minimum stock level" also saved in the table. The query is pretty intense and there are over 4,000 records now to check against. These querys normally take about 75s. So I have made a little label that gets updated every time the form is loaded showing the time and date the query was last run, and the duration in seconds it took. (so users can see how fresh the data is and decide if it needs to be run again)

Now, the weird thing is it works fine in my Access client, but when I sync my changes to the server and try it in a web browser I get a "type mismatch" error. A small table is used to store the start and end times whenever the query is run, that's how I get the timestamp data. These fields are in a "Date/Time" format, obviously. But it seems the problem here is changing the date format to a string format so it can be put in a label on the form. The Access client seems perfectly capable of doing this, while the web client stumbles and falls.

My problem is, how do I change data in a date/time format to a string format in a Web database? I can't figure out how to do this. The tools are so limited. I may have to end up answering my own question here but I'm posting this for others just in case.

Improve this question
6
  • I am just throwing an idea out there because I haven't used Web Db before - but did you try Cstr() or CDate()? You could try and make the label a text box and add type conversion functions to it (if it's allowed).. Commented Nov 12, 2014 at 19:04
  • You can do this with server side code, but not browser side. Forms code runs 100% in the browser (including those on say an iPhone – these browsers thus have limited functions and features). You don’t say when or how or where you are grabbing this date/time value. If you UI macro (forms macro) is calling a data macro and that data macro returns values, then you can get date data back as a string. Why not just use a text box formatted as date/time as opposed to a label? However until we know where you grab this value from, then answers are speculative. So server side code can do this. Commented Nov 12, 2014 at 19:14
  • @Invent-Animate unfortunately Access Web db's don't get Cstr() or CDate() to work with, I'm only allowed CDbl(). This is why the "Web DB" feature of Access can be such a bad idea unless you already know about all the limits and restrictions. Both of you were right it turns out, it's much much easier to use a text field with date formatting, modified to work like a label. I will post and answer to show Commented Nov 12, 2014 at 19:23
  • 1
    @ITBear Thanks for the update. Sorry I can't give you an answer. Commented Nov 12, 2014 at 19:25
  • @Invent-Animate I appreciate the quick help! "Web DB"-problems are usually too niche to get many replies :) Commented Nov 12, 2014 at 19:30

2 Answers 2

Reset to default
2

To return a value from a data macro as string, you have to format the internal date/time format as a string. In Access an internal date/time value is a double number with the integer part as number of days since 1900, and the “decimal” time part is a fraction of 24 hours. Unfortunately if you simply wrap the date/time in the str$() function we had for 20+ years, then you get something JUST like if you type this into the debug window:

? cdbl(now())
 41955.5478587963

The solution is to simply pull out each part. And “nice” is while in few cases a data macro will cast the data type, it does in this case and thus the STR$() command is not required.

The expression you thus can use is this:

Month([d]) & "/" & Day([d]) & " Time = " & Hour([d]) & ":" & Minute([d])

So say to pluck out the VERY LAST start time column from say a invoice table, since we don’t have a dmax(), then we simply sort the table in the order we want and pull out the first row.

Our data macro will thus look like: enter image description here

Note how in above I simply typed in the SQL and SET the order on the date/time column. I want the MOST recent invoice start date and time. For those new to SQL, then I suggest you build a query in the query builder and specify a query in above lookup feature, since many are not "comfortable" typing in free hand SQL as I did above.

Now, in your browser side (UI) macro, you can use this code:

enter image description here

The above returns a formatted string that you can stuff into a text box, or as per above code change the caption of a label.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I see exactly now! It makes my expressions more complex but does exactly what I wanted: turn a "date/time" value into a "string"! I'm testing it now but this looks like money. I'll let you know how it goes in a few mins
Muchos gracias good sir, I owe you +1 internets!! Not only did it work fabulously the first time, I named my var "d" and literally copied-and-pasted your expression as-is into my app and it worked!: Last Run string: 11/12 Time = 14:13 Kudos and thank you!
0

Unfortunately with silly problems like this, it becomes a path-of-least resistance thing.

Since my intended result was simply to get "a timedatestamp from a table to show up on a form (so users could see when a query was last run)", this became redesigning my form in Access to be a text field instead of a label. Text fields can be adjusted to accept "Time/Date" formats, so this is exactly what I did, it now pulls the timestamp data directly from the last record of the table and requires no extra formatting to appear in the web browser. I redesigned the text field to appear and function more like a label, and my desired function was achieved.

However, since my question specifically asks, "how do you change a time/date format into a string format in a Web db?", I will leave it here in case someone actually does solve it.

Improve this answer

4 Comments

You can do this if the value is being returned from server side code (data macro). You don't mention how you grabbing this value. Browser side you code has to run on that iPhone and last time I looked the browser on that phone does not have reocrdsets or the required conversion function(s)
I tried converting the value in a data macro before passing it back to the form, but wasn't getting much luck. Do you think it would work if I wrote the value back to the table in a 'string' field (and pulled again as string)?
@AlbertD.Kallal You got me to testing now just so I can be thorough. It appears to me that even in Data Macros (which are executed server-side) I cannot coerce any date value I get from Now() into being a string, no matter if I try to write it to a text-field in the same table, use Trim(Now()) or Left(Now(),30) which are pretty much all I get as far as 'string' operations go, even in Data Macros. :'( I got the date/time values from a Data Macro on the table, once before running the query (start time) and once after. I do appreciate all the help thus far tho!
I have to write this up as a answer - you not grasping how this works at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.