Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Seems to me the first challenge is reading your custom string as a valid date. In <a href="https://stackoverflow.com/questions/7209274/mismatched-datatype-in-access-query">your previous question</a>, you gave this as a sample string stored in your [AlarmStart] field:</p> <pre><code>Tue Jan 18 10:10:57 2011 </code></pre> <p>The problem is VBA doesn't recognize that string as containing a valid Date/Time value. </p> <pre><code>? IsDate("Tue Jan 18 10:10:57 2011") False </code></pre> <p>However, if you revise that string (drop the day of the week and move year before time), you can produce a string which VBA recognizes as a valid date.</p> <pre><code>? IsDate("Jan 18 2011 10:10:57") True </code></pre> <p>So you can use a function to split apart the string, rearrange the pieces you need, and return a Date/Time value. </p> <pre><code>Public Function DateFromCustomString(ByVal pIn As String) As Variant Dim varPieces As Variant Dim strNew As String Dim varReturn As Variant varPieces = Split(pIn) strNew = Join(Array(varPieces(1), varPieces(2), varPieces(4), _ varPieces(3)), " ") If IsDate(strNew) Then varReturn = CDate(strNew) Else varReturn = Null End If DateFromCustomString = varReturn End Function </code></pre> <p>(The Split() and Join() functions are available starting with Access 2000.)</p> <p>And, once you have a Date/Time value from your string, you can use the Format() function to display it however you like.</p> <pre><code>Format(DateFromCustomString(alarmdet.AlarmStart), "ddd MMM dd yyyy hh:mm:ss") AS AlarmDateTime Format(DateFromCustomString(alarmdet.AlarmStart), "mm/dd/yyyy") AS AlarmDate </code></pre> <p>This works as described with English month name abbreviations. I don't know what will happen with Portuguese. </p>
    singulars
    1. This table or related slice is empty.
    1. POConvert a Text to a DateTime in access
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USHansUp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POConvert a Text to a DateTime in access
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI've tried using this function which looks like it should work, however i am getting an error when running the query: Undefined function DateFromCustomString I've checked the spelling and made sure the module name it's in is not the same. i've even tried pulling the function from the builder in access which is does, but still get the same error. Any idea why that might be?
      singulars
      1. PO
      1. USJames213
    2. COIs the function recognized when you try it in the Immediate Window like this? ... `Debug.Print DateFromCustomString("Tue Jan 18 10:10:57 2011")`
      singulars
      1. PO
      1. USHansUp
    3. COYes when i run the above statement it outputs to: `1/18/2011 10:10:57 AM`
      singulars
      1. PO
      1. USJames213
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload