<div>
Use TimeSerial(Hour, Minute, Second). E.g.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42355766-1"><span>ShiftFrom: TimeSerial(Shift_From_Hour, Nz(Shift_From_Minute, 0), 0)</span>
</code></pre>
<br />
in a query.
</div>


<div>
@ste5an,<br />
<br />
That gives me an extra two digits for the seconds.<br />
How can I get rid of them?<br />
Also when both are null I get #Error.<br />
<br />
Thanks,<br />
Ben
</div>


<div>
<pre><code class="code-10 nolinks" id="code-20-42355802-1"><span>$isHourNull = [String]::IsNullOrEmpty($Shift_From_Hour)</span>
<span>$isMinuteNull = [String]::IsNullOrEmpty($Shift_From_Minute)</span>
<span></span>
<span>#4 cases</span>
<span>$today=[Datetime]::Now</span>
<span>if($isHourNull -and $isMinuteNull){</span>
<span>    $result=New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,0,0,0)</span>
<span>}</span>
<span>elseif(!($isHourNull) -and $isMinuteNull){</span>
<span>    $result=New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,$Shift_From_Hour,0,0)</span>
<span>}</span>
<span>elseif(!($isMinuteNull) -and $isHourNull){</span>
<span>    $result=New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,0,$Shift_From_Minute,0)</span>
<span>}</span>
<span>else{</span>
<span>    $result= New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,$Shift_From_Hour,$Shift_From_Minute,0)</span>
<span>}</span>
<span>Write-Host $result.ToString(&quot;t&quot;)</span>
</code></pre>
</div>


<div>
@Jose,<br />
<br />
I need this in VB/A, as this is to be used as a control source for a control in an Access report.<br />
<br />
Thanks,<br />
Ben
</div>


<div>
Jum that wasn't specified in the question. Good luck :)
</div>


<div>
Please try like this -<br />
<br />
Dim LString As String<br />
Dim LArray() As String<br />
<br />
LString = Split (TimeSerial(Nz(Shift_From_<wbr />Hour,0), Nz(Shift_From_Minute, 0), 0), &quot;.&quot;)<br />
LArray = Split(LString)<br />
<br />
LArray(0) + ':' + LArray(1) &nbsp;+ ' ' + RIGHT( LArray(2), 2 )
</div>


<div>
Hi Pawan,<br />
<br />
You mean I should create a function with two params?<br />
<br />
Thanks,<br />
Ben
</div>


<div>
Trying the following and getting error &nbsp;&quot;Type mismatch&quot;<br />

<pre><code class="code-1 nolinks" id="code-20-42355844-1"><span>Function TestPawanDate(i1 As Variant, i2 As Variant)</span>
<span>Dim LString As String</span>
<span>Dim LArray() As String</span>
<span></span>
<span>LString = Split(TimeSerial(Nz(Shift_From_Hour, 0), Nz(Shift_From_Minute, 0), 0), &quot;.&quot;)</span>
<span>LArray = Split(LString)</span>
<span></span>
<span>TestPawanDate = LArray(0) + &quot;:&quot; + LArray(1) + &quot; &quot; + Right(LArray(2), 2)</span>
<span>End Function</span>
</code></pre>
<br />
Thanks,<br />
Ben
</div>


<div>
@Norie,<br />
<br />
Okay, that solved the extra seconds issue.<br />
<br />
However now it always returns &quot;00&quot;, even if has hour and no min (which in that case it should be the hour with 00)<br />
or in case if both are nulls then it should be blank.<br />
<br />
How can these be adjusted?<br />
<br />
Thanks,<br />
Ben
</div>


<div>
Not sure I'm following all your specifications but try this.<br />

<pre><code class="code-1 nolinks" id="code-20-42355860-1"><span>x = IIf(IsNull(Shift_From_Hour) And IsNull(Shift_From_Minute), &quot;&quot;, Format(TimeSerial(Val(Nz(Shift_From_Hour, &quot;00&quot;) &amp; &quot;&quot;), Val(Nz(Shift_From_Minute) &amp; &quot;&quot;), 0), &quot;hh:mm AM/PM&quot;))</span>
</code></pre>
</div>


<div>
I simply adjusted for the following<br />

<pre><code class="code-1 nolinks" id="code-20-42355862-1"><span>IIf(nz([Shift_From_Hour],0)=0,&quot;&quot;,Format(TimeSerial(Val([Shift_From_Hour] &amp; &quot;&quot;),Val([Shift_From_Minute] &amp; &quot;&quot;),0),&quot;hh:mm AM/PM&quot;))</span>
</code></pre>
<br />
Thanks,<br />
Ben
</div>


<div>
<div class="content wysiwyg-content">
Hi Experts,<br />
<br />
I have two numbers fields, Shift_From_Hour and Shift_From_Minute<br />
They store hours and minutes.<br />
<br />
How do I display them in <b>&quot;01:45 PM&quot;</b> format, taking in consideration that the minutes field can be null or zero, which in that case it should be 00.<br />
<br />
In addition if both are null or the hour field is null, it should just be be blank.
</div>
</div>


Hi Experts,

I have two numbers fields, Shift_From_Hour and Shift_From_Minute
They store hours and minutes.

How do I display them in "01:45 PM" format, taking in consideration that the minutes field can be null or zero, which in that case it should be 00.

In addition if both are null or the hour field is null, it should just be be blank.

How to convert two numbers into date format

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.