Dlookup与时间标准 [英] Dlookup with time criteria

问题描述

我有一张只列出时段的表格。每隔15分钟从上午9:00至下午5:00。 TBTime中[时间]的格式为中等时间。


例如TBTime

TimeID___Time

___ 1 _____ 9:00

___ 2 _____ 9:15 am

___ 3 _____ 9:30

我想根据[时间] Dlookup [TimeID]。


当我使用代码时：

I have a table which just lists time slots. Every 15 mins from 9:00am - 5:00pm. The format of [Time] in TBTime is ''Medium Time''.

e.g. TBTime
TimeID___Time
___1_____9:00am
___2_____9:15am
___3_____9:30am

I want to Dlookup the [TimeID] based on the [Time].

When I used the code:

展开 | 选择 | Wrap | 行号

推荐答案

Reginald，


我无法解释发生了什么，但试试这个。在模块中创建此公共子，并观察从即时窗口运行时发生的情况。

Reginald,

I can''t explain what is happening, but try this. Create this public sub in a module and watch what happened when you run it from the immediate window.

< span class =codeLinkonclick =Blur（this，this.parentNode.parentNode，getChildren（this），true）;>展开 | 选择 | Wrap | 行号

我想如果Time是一个保留词，Access就不会让我使用它。但是我创建了重复的表并将字段重命名为[TimeSlot]，但这并没有区别。


我明白你的来源，可能是存储日期但你的调试代码我可以看到没有存储日期。


这是结果

1899年12月30日00:15:00 _____ 2 _____ [TimeID = 2]

1899年12月30日，13：53：11 _____ 58 ____ [时间=时间（）]

18年12月30日，09：15：00 _____ 39 ____ [时间> Me.NextAvaliablePickupTime]

1899年12月30日，09：15：00 _____ 1 _____ [时间< Me.NextAvaliablePickupTime]

1899年12月30日09:15:00 _____空___ [TIME = Me.NextAvaliablePickupTime]


最后3个使用Me.NextAvaliablePickupTime，更多bizzarly使用''>''符号似乎工作并且使用''=''或''<''不会?????????

I thought if Time was a reserved word Access wouldn''t let me use it. But I created duplicate table and renamed the field to [TimeSlot] but this made no difference.

I understand where your coming from, could be that a date is being stored but with your debug code i can see that a date is not being stored.

This is the results
30 Dec 1899, 00:15:00_____2_____[TimeID = 2]
30 Dec 1899, 13:53:11_____58____[TIME = Time()]
30 Dec 1899, 09:15:00_____39____[TIME > Me.NextAvaliablePickupTime]
30 Dec 1899, 09:15:00_____1_____[TIME < Me.NextAvaliablePickupTime]
30 Dec 1899, 09:15:00_____Null___[TIME = Me.NextAvaliablePickupTime]

the last 3 use Me.NextAvaliablePickupTime, even more bizzarly using the ''>'' sign seems to work and using ''='' or ''<'' does not ????????

我只是想到了什么.....


如果你有TBTime表，每次都有索引，那么你可以使用TBTable在你的表单上有一个组合框作为该组合框的行源？然后，有人从组合框中选择一个时间，组合框使用索引而不是时间。


我不确定用户如何输入NextAvaliablePickupTime，但这会消除时间上的任何格式问题。

I just thought of something.....

If you have your TBTime table, with and index for each time, would it be possible to have a combo box on your form, using the TBTable as the row source for that combo box? Then, someone selects a time from the combo box, and the combo box uses the index, rather than the time.

I''m not exactly sure how the user inputs the NextAvaliablePickupTime, but this would eliminate any format issues with the time.

这篇关于Dlookup与时间标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！